Solved: Time Intelligence - WTD and MTD Previous Period - Page 2

Anonymous · ‎08-23-2016

Hi. I am trying to compare some values with the previous week and previous month. I want to compare similar periods, so if the current week has only 3 days, it should compare with the first 3 days of the previous week. The same goes for the month.

For the week, I have this measure:

Weekly Evolution = (SUM(NG_Consumptions[Consumption])-CALCULATE(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-7,DAY)))/CALCULATE(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-7,DAY))

And it gives the value of the incomplete previous week, which is good, it compares similar periods.

But when I do that for the month, with the formula below, it compares the whole previous month with the current month, different periods.

Monthly Evolution = (TOTALMTD(sum(NG_Consumptions[Consumption]),'Calendar'[Date])-TOTALMTD(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-1,MONTH)))/TOTALMTD(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-1,MONTH))

Any idea why this happens?

Thanks!

CheenuSing · ‎08-28-2016

@Anonymous

I find that column YearMonthNumber has not been defined in the Date Table. This should be done as

YearMonthNumber = ('Calendar'[Year] - MIN( 'Calendar'[Year] )) * 12 + 'Calendar'[MonthNumber]

Also create a column in the Date table as Datekey = [Year]*10000+[MonthNumber]*100+Day([Date])

Similarly create a DateKey column in Fact table as

(Year(Factable[TransactionDate])*10000+Month(Factable[TransactionDate])*100+day(Factable[TransactionDate]) )

Replace the Factable by your table name and the transactiondate by the date column of the facttable. I am assuming TransactionDate is in the format ( DD/MM/YYYY) format.

It should work.

You can try to attach a excel file with sample data or mail it srini@samiksha.com.sg

Let me know if this works.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post