Month to Date and Year To Date

[VijayReddy]

Hi all,

I am trying to add two measures to our Power BI Tabular model.  We have the required Facts and Dimensions with proper dates table.

I need to compare Revenue this month (which I already have) versus Revenue same month last year (Revenue MTD Last Year). But the catch is, it should compare up until the same day of month last year that of current month. (Example: When I am comparing current month Revenue, April 2018 with April 2017, it should return Revenue for April 2017 until today's date, 25 April).

Likewise, I need YTD measure as well (Revenue YTD Last Year), since I may have to use these two measures in seperate graphs.

Can someone help me with the formulas?

Thank you.

[Floriankx]

According to official support site for TOTALMTD it is possible to add a filter expression.

So you could TOTALMTD([Revenue];[Date];SAMEPERIODLASTYEAR([Date])).

I haven't tried out. But this should be the way to go.

If this doesn't work you can try it stepwise.

Revenue_LY:=CALCULATE([Revenue];SAMEPERIODLASTYEAR([Dates]) and then

Revenue_LY_MTD:=TOTALMTD([Revnue_LY];[Dates]

Please give it a try and let me know

[VijayReddy]

Hi @Floriankx

Thank you for the response.

TOTALMTD function is returning full month revenue for last year same period.  Instead, I need revenue until same date as of current month.

My formula for last year revenue is: Revenue LY = CALCULATE(SUM('Invoiced Trans'[Revenue]), DATEADD('Calendar'[DateID],-1,YEAR))

And, for Last Year MTD is, as per your suggestion:

Revenue LY MTD = TOTALMTD(CALCULATE('Invoiced Trans'[Revenue LY]),'Calendar'[Date]) which is returning full month revenue from last year.

[Floriankx]

Hello do you have selected the 28th of April anywhere so BI can know it to which date there is to built the MTD?

[VijayReddy]

Hi, 

I didn't but after reading your message, I have tried selecing today's date and it seems to be working. However, my concern would be selecting today's date every day, which does not seem to be an ideal solution since this dashboard is for a CEO.  Can we perhaps not use some function in DAX to keep it dynamic?

Thank you for your time.

[Floriankx · Accepted solution]

Hello,

do you have a fact table where always have data till the actual day?

There might be two possibilities.

But before that I think your CALCULATE her is obsolete, because you don't apply any filter.

Revenue LY MTD = TOTALMTD(CALCULATE('Invoiced Trans'[Revenue LY]),'Calendar'[Date])

Revenue LY MTD =
TOTALMTD([Revenue],
 DATEADD(
  Filter(
   DATESMTD('Calendar'[Date]),
   'Calendar'[Date]<TODAY()),
 -1,YEAR))

Or you could try to replace TODAY() by LASTDATE('Invoiced Trans'[Date].

I found the DATESMTD just myself, haven't used it before. I would have used VALUES instead but result seems to be the same.

Maybe you can have a look here

[VijayReddy]

Hi

@Floriankx Thank you so so much for the solutiion. It is working well.

I have created a financial YTD as well, where our financial ends June 30, as below and the formula is working fine.

Revenue PYTD = CALCULATE(SUM('Invoiced Trans'[Revenue]), DATEADD(FILTER( DATESYTD('Calendar'[Date], "30/6"),'Calendar'[Date] < TODAY()), -1, YEAR))