cancel
Showing results for
Search instead for
Did you mean:
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
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!
13 REPLIES 13
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.
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.

Highlighted
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!
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.

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

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

## Re: Time Intelligence - WTD and MTD Previous Period

@CheenuSing

Perfect! It works.

Thanks so much for your help.

Regards.

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 Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)