Sameperiodlastyear showing entire month instead of mtd

[vonw13]

I used a measure to take the total sales mtd, then a new measure to use sameperiodlast year, but it shows the full month of May 2021 instead of mtd previous year. How do I get it do accuratlely show through mtd previous year? I have slicers on my report for month and year.

Sales data PY using manual slicers

Current year MTD showing accurately

PY MTD showing entire month

[themistoklis · Accepted solution]

Hello @vonw13 

Try the following formula. Make sure you have a calendar table.

Turnover MTD (PY) = CALCULATE(SUM('Table'[TurnOver]),
        DATESBETWEEN('Calendar'[Date],                    
            FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)),   
            IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) 
         )
     )

[vonw13]

That worked! Thank you so much! Do you know why just the regular sameperiodlast year or the other functions didn't work?

[themistoklis]

To be honest I dont know the answer to this question. 

There are alternatives on calculating the MTD for previous year but I found the above formula the one that covered my needs.

May worth trying the formula from this post as well.

https://community.powerbi.com/t5/Desktop/MTD-Comparison-to-same-period-last-year/m-p/188900

[speedramps]

Please watch this series of free BI training videos inlcuding a free calendar download.

Then you can sum, filters and compare MTD, YTD, LM, LY, ect like a rock star.

Click here for free videos 

Please click thumbs up and accept as solution. 😀