How to Calculate YOY for Incomplete Month Data?

Sept 2019 Sales Data till 09/09/2019
Sept 2018 – Full Month Sales Data

A very common situation when you don’t have full month data to calculate YOY ( Year-on-year), MOM( Month-on-month). You will end up with comparing incomplete month with full month data for the previous year. A simple way to compare current year sales with previous year is to use SAMEPERIODLASTYEAR() functions in DAX. But this will give incorrect grand total as SAMEPERIODLASTYEAR() will compare full previous month sales to current month partial sales data.  

Wrong_PY_Sales using SAMEPERIODLASTYEAR() functions in DAX
Wrong_PY_Sales = 
VAR PreviousRange = SAMEPERIODLASTYEAR(Dates[Date])
RETURN
CALCULATE(
   SUM(data[Sales]),
    PreviousRange 
)
PY_Sales = 
VAR LDate =EDATE(CALCULATE(MAX(Dates[Date]),ALL(Dates)),-12)
VAR PreviousRange = SAMEPERIODLASTYEAR(Dates[Date])
RETURN
CALCULATE(
   SUM(data[Sales]),
    FILTER(PreviousRange,Dates[Date]<=LDate)  
)
Difference in YOY
Final Results

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.