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

MTD LY Same time period

Hi,

 

Im trying to compare the MTD of the current period with the relevant MTD of the period LY. The issue I am experiencing is the period LY has a full years worth of data and thus trying to perform a LFL comparison in the month you are in is not accurate. In order to get around this I have performed the below:

 

MTD TY formula:

 

GB_MTD:=TOTALMTD([GB],Cal[Date])

 

MTD LY: GB_MTD_LY2:=IF(LASTDATE(Cal[Date])>TODAY()-1,BLANK(),
CALCULATE([GB_MTD],DATEADD(Cal[Date],-1,YEAR)))

 

After completing this I have had the following issues. When looking at it from a days POV everything looks fine: the LY formula cuts it off as I expect at todays date, however - once I look at it monthly I lose the data for the month that is not completed.

 

I have made sure the calendar table has all the relavant dates for the month concerned - is this something to do with using datesadd for months that are not completed?

 

I am new to the forum and can post examples of the issue I am having - just not sure how to do so.

 

Any advice wiould be GREATLY appreciated for a new to the forum and pretty new to PBI user.

 

Thanks

 

Shane 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @Heuers ,

 

I have created a sample for your reference.

 

GB_MTD = CALCULATE(SUM('Table'[value]),DATESMTD('date'[Date]))
LY = TOTALMTD(CALCULATE(SUM('Table'[value]),SAMEPERIODLASTYEAR('date'[Date])),'date'[Date])

dm.PNG

 

Pbix as attached.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@Heuers , try like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last year MTD Sales =
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)),'Date'[Date]<=_max)

 

In place of Today, You can use Max date of you fact



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak 

 

Thanks for the reply - tried your solution below and its works great for comparing the same LFL period.

 

I can now perform MTD vs MTD LY calculation.

 

Could you please provide me with a reason why the following happens: I have a pivot connected to that model: If a blank out from a certain day as in my orginal soloution, the days blank out correctly, why does the month total also become blank whilst there is data for the corresponding days under it? 

 

Measure 1 is your solution blanking out from 27/04 onwards. LY measure is your solution with no blank.

 

Thanks

 

 

1.PNG

 

 

 

 

Highlighted
Community Support
Community Support

Hi @Heuers ,

 

I have created a sample for your reference.

 

GB_MTD = CALCULATE(SUM('Table'[value]),DATESMTD('date'[Date]))
LY = TOTALMTD(CALCULATE(SUM('Table'[value]),SAMEPERIODLASTYEAR('date'[Date])),'date'[Date])

dm.PNG

 

Pbix as attached.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors