Problem in Calculating PMTD(Prev Month To Date) Value

[Former Member]

Hi All

I have created a measure(PMTD) in Universe .My query always gets the previous months data. But when i try to pass Jan-13, it should get the data for Dec-2012... but it is failing to do so.

Please find below, the formula i used

Sql Syntax :

sum(case when DATE_DIMENSION.MONTH={fn month(@Prompt(Date))} -1 and {fn dayofmonth(DATE_DIMENSION.GENERATED_DATE)}<={fn dayofmonth(@Prompt(Date))} then FT_INV_DETAIL.SALESQTY else 0 end

Please help me out

[former_member4998]

Hi..

Please find the below sample code. It will be applicable for all the months whatever month you entered…it will give the last month data. And also if you implement the logic at the universe level…it will improve the performance also.

select

DATE_ID as DATE_ID,

'Last Month' as Periods

from td_Date where

td_Date.CalendarDate BETWEEN  dateadd(m,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime))

and dateadd(d,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime))

union all

[Former Member]

You will need to handle the first month of year separately for your logic to work in all condition..

Try the below logic and improvise it to suit your case which will work in all cases..

sum(

CASE WHEN {fn month(@Prompt(Date))} = 1  then CASE when {fn month(@Prompt(Date))} = 12 and DATE_DIMENSION.Year = {fn Year(@Prompt(Date))} -1 and and {fn dayofmonth(DATE_DIMENSION.GENERATED_DATE)}<={fn dayofmonth(@Prompt(Date))} then FT_INV_DETAIL.SALESQTY ELSE

case when DATE_DIMENSION.MONTH={fn month(@Prompt(Date))} -1 and {fn dayofmonth(DATE_DIMENSION.GENERATED_DATE)}<={fn dayofmonth(@Prompt(Date))} then FT_INV_DETAIL.SALESQTY else 0 end