cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
svalen Member
Member

Time Intelligence - WTD and MTD Previous Period

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!

1 ACCEPTED SOLUTION

Accepted Solutions
CheenuSing Super Contributor
Super Contributor

Re: Time Intelligence - WTD and MTD Previous Period

@svalen

 

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

13 REPLIES 13
Super User
Super User

Re: Time Intelligence - WTD and MTD Previous Period

I think your issue is that you are time shifting on an increasing granularity. So if I said, today is 2 April, what month is it now, what are the sales this month, and what were the sales last month - you might say - this month is April, our sales this month are X, and the sales last month (for March) were yada yada.  If today is 30th April and I asked the same question, the answer for last month would be the same but the answer for April would be different. So are you wanting to compare month to date this month with month to date last month?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
svalen Member
Member

Re: Time Intelligence - WTD and MTD Previous Period

Thanks Matt.

 

Yes, given your example, I am trying to compare 1 and 2 of April with 1 and 2 of March, or 1 - 30 April with 1 - 30 March.

 

Is it possible?

 

------------------------------------------------------------------------------------------------------------------------------

 

PS. I also tried with DATEADD, -X, DAYS but as months have different day number it didn't work out very well.

CheenuSing Super Contributor
Super Contributor

Re: Time Intelligence - WTD and MTD Previous Period

@svalen

 

I am assuming you have a date table containing all the dates and it is linked to the SalesFact Table.

Also your date table has dates upto the lastdate of the SalesDate and not beyond. I am copying below the steps that I learnt from one of the links on time intelligence patterns from internet. I do not have the link to share.

 

To achieve your results

 

1. Add coulmns to the Date table as under

    a) YearNumber = Year('Date'[Date])

    b) MonthNumber = Month('Date'[Date])

    c) MonthDayNumber=DAY('Date'[Date])

    d) MonthDays = Day(EOMONTH([Date],0))

 

2. Add a column called PMDate to the date table

   =
                        CALCULATE (
                                   MAX ( 'Date'[Date] ),
                                                 ALL ( 'Date' ),
                                                   FILTER (
                                                    ALL ( 'Date'[MonthDayNumber] ),
                                                       'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] )
                                                               || EARLIER('Date'[MonthDayNumber] ) = EARLIER ( 'Date'[MonthDays] )
                                                               ),
                                                         FILTER (
                                                                          ALL ( 'Date'[YearMonthNumber] ),
                                                                                           'Date'[YearMonthNumber]
                                                                         = EARLIER ( 'Date'[YearMonthNumber] ) - 1
                                                                      )
                                           )

    

3. Create a measure called                 Sales:=SUM(SalesFact[SalesAmount])

 

4. Create a measure called     MTD Sales=CALCULATE (
                                                                       [Sales],
                                                                                    FILTER (
                                                                                               ALL ( DATE ),
                                                                                               'Date'[YearMonthNumber] = MAX ( 'Date'[YearMonthNumber] )
                                                                                                    && 'Date'[Date] <= MAX ( 'Date'[Date] )
                                                                                            )
                                                                                        )

    This will calculate the for the Month to date.  If your last date of sales is Aug 23 2016 then the MTD Sales will be from 1 aug 2016 to 23 Aug 2016.

 

5. Create a measure called 

                                    PMTD Sales:=CALCULATE (
                                                                                [Sales],
                                                                                          FILTER (
                                                                                                        ALL ( DATE ),
                                                                                                   'Date'[YearMonthNumber] = MAX ( 'Date'[YearMonthNumber] ) - 1
                                                                                                        && 'Date'[Date] <= MAX ( 'Date'[PM Date] )
                                                                                                           )
                                                                                     )

      This will calculte the previous month to date sales. In the example it will be for 1 Jul 2016 to 23 Jul 2016.

 

6. Even if you have slicers on Year, Month and Day for users to select the above measures will calculate the MTD and Previous Month to Date values correctly. 

 

Try it out and if works please accept it as a solution and also give Kudos.

 

Cheers 

 

CheenuSing

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

Proud to be a Datanaut!
svalen Member
Member

Re: Time Intelligence - WTD and MTD Previous Period

Sing,

 

I tried your solution and apparently it doesn't work as expected.

 

Please see the table below. The value for MTD Consumption should be the same as the Sum and it isn't. For the PMTD the data is also diverging.

 

Any tips?

 

Thanks for your assistance.

 Untitled.png

CheenuSing Super Contributor
Super Contributor

Re: Time Intelligence - WTD and MTD Previous Period

@svalen

 

Can you please share the expressions you had written, your data model relationships and sample data if possible

 

Cheers

CheenuSing

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

Proud to be a Datanaut!
svalen Member
Member

Re: Time Intelligence - WTD and MTD Previous Period

@CheenuSing

 

Here is some information.

 

PMDate = CALCULATE(
MAX('Calendar'[Date]),
ALL('Calendar'),
FILTER(
ALL('Calendar'[Day]),
'Calendar'[Day]<=EARLIER('Calendar'[Day])
|| EARLIER('Calendar'[Day])=EARLIER('Calendar'[MonthDays])
),
FILTER(
all('Calendar'[Month]),
'Calendar'[Month]
=EARLIER('Calendar'[Month])-1
)
)

 

MonthDays = DAY(EOMONTH('Calendar'[Date],0))

 

Sum Consumption = SUM(NG_Consumptions[Consumption])

 

PMTD Consumption = CALCULATE(
[Sum Consumption],
FILTER(
all('Calendar'),
'Calendar'[Month] = MAX('Calendar'[Month]) -1
&& 'Calendar'[Date] <= MAX('Calendar'[PMDate])
))

 

MTD Consumption = CALCULATE(
[Sum Consumption],
FILTER(
all('Calendar'),
'Calendar'[Month] = MAX('Calendar'[Month])
&& 'Calendar'[Date] <= MAX('Calendar'[Date])
))

 

I have a Calendar table with all the dates connecting everything.

 

Regarding giving you the data, sorry for my ignorance, but is ther any expedite way to send you the file with data from 2 or 3 months, for example? Currently I don't know how to do this without sending all the data from the backgroung.

 

Untitled.png

CheenuSing Super Contributor
Super Contributor

Re: Time Intelligence - WTD and MTD Previous Period

@svalen

 

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

svalen Member
Member

Re: Time Intelligence - WTD and MTD Previous Period

@CheenuSing

 

Perfect! It works.

 

Thanks so much for your help.

 

Regards.

svalen Member
Member

Re: Time Intelligence - WTD and MTD Previous Period

@CheenuSing

 

Cheenu,

 

Having accomplished my goal with the current MTD measure with your help, could you please me give me a hint about how I could do the same for the YTD measure?

 

Thanks in advance.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)