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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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