cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VijayReddy Frequent Visitor
Frequent Visitor

Month to Date and Year To Date

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Floriankx Established Member
Established Member

Re: Month to Date and Year To Date

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

6 REPLIES 6
Floriankx Established Member
Established Member

Re: Month to Date and Year To Date

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 Frequent Visitor
Frequent Visitor

Re: Month to Date and Year To Date

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

Re: Month to Date and Year To Date

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 Frequent Visitor
Frequent Visitor

Re: Month to Date and Year To Date

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

Re: Month to Date and Year To Date

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 Frequent Visitor
Frequent Visitor

Re: Month to Date and Year To Date

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))