Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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

13 REPLIES 13

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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.

@Anonymous

 

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!
Anonymous
Not applicable

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

@Anonymous

 

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!
Anonymous
Not applicable

@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

@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!

This is formula is key to resolving date calculations.  It absolves the 12 month timeframe and allows to do simple calculations to add or remove months. 

 

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

 

YearMontNumber should not be just YYYY-MM.

 

Thank you!

 

 

Anonymous
Not applicable

@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.

@Anonymous

 

Please do the following

1. Create a column in the Calendar /Date table as

            PYDate = CALCULATE (  DAteadd('Calendar'[Date],-1,Year)  )

 

2. Create a measure called  SalesThisYear

    SalesThisYear = CALCULATE (
                                                  [SalesAmount] ,
                                                                         FILTER (
                                                                                   ALL ( 'Calendar' ),
                                                                          'Calendar'[Year] = MAX ( 'Calendar'[Year] )
                                                                          && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                                                      )
                                                )

  This will give the Sales cumulated uptodate for the current period.

 

3. Create a measure called SalesLatYear

                                                     SalesLastYear = CALCULATE (
                                                                                          [SalesAmount] ,
                                                                                                              FILTER (
                                                                                                                ALL ( 'Calendar' ),
                                                                                                  'Calendar'[Year] = MAX ( 'Calendar'[Year]) - 1
                                                                                                   && 'Calendar'[Date] <= MAX( 'Calendar'[PYDate] )
                                                                                                      )
                                                                                                  )

 

If it works please give Kudos.

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!
Anonymous
Not applicable

@CheenuSing, hi i have a similar problem. I use your formula but when i select for example february I have accumulated what I have in January and February sales, could you please help me in doing something so I do not accumulate sales if I have only one month selected in my filter, if I have several months filtered then if I accumulate these sales.

Anonymous
Not applicable

@CheenuSing

 

Perfect. Thanks. I should have adapted the logic from the previous measure, now I realise it is simple.

 

Thanks for your support.

Anonymous
Not applicable

@CheenuSing

 

Perfect! It works.

 

Thanks so much for your help.

 

Regards.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.