DATEADD function (DAX) for previous year by slicer

[Ikigai]

I have a dataset that starts in July 2023 and updates each month.  Lastest refresh is up to November 2024.  December 2024 is still needing to come in.  I am using the DATEADD measure to look at previous year customer averages for any given period depending on the Year/Month the end user chooses in a slicer.

The function I'm using is:

Prior Year Average = CALCULATE(Customers[Average],DATEADD('Date Table'[Date],-1,YEAR)))

In general, this is giving me what I need.  When I select a specific month in the slicer, the function gives me the previous year's average for that specific month.  When I select a set of months, it returns the previous year's averages for each respective month and averages them together.

Example #1: 

I select Jul and Oct 2024 (yellow) in the slicer...it averages both prior year's 2023 averages for those months (blue).  All is good.  Just what I'm looking for.

I run into an issue, however, when nothing is selected in the slicer, or I select all of 2024.  In both scenarios, the function references and collectively averages only months July 23 to November 2023 (yellow) from the prior year.  It does not include the average for December 2023, which would then return the entire average for year 2023 (blue).

Example#2:

 

I am assuming this is because December 2024 data doesn't exist in the dataset yet, and thus there is no "Dec 2024" option in the slicer yet to reference back to "Dec 2023."

What I would like is for the user to be able to retain the functionality in Example# 1 (i.e., choose different months and obtain the prior year's averages for those months)....but also, in Example #2, when they select an entire year (like the 2024 checkbox), it returns everything for the prior year.  Basically, the blue.

I am new to Power BI and DAX so I am not sure if this is achieveable using this function.

If not, would another function work better, or can some workaround be applied?

Any insight is greatly appreciated.  Thank you!

[danextian · Accepted solution]

Hi @Ikigai 

Please try this:

PreviousYear2 = 
VAR FullYear =
    CALCULATE (
        [Total Revenue],
        FILTER ( ALL ( Dates ), Dates[Year] = MAX ( Dates[Year] ) - 1 )
    )
VAR LastYear =
    CALCULATE ( [Total Revenue], DATEADD ( Dates[Date], -1, YEAR ) )
VAR MonthSelectedTest =
    --test if allselected months less than all months
    COUNTROWS ( ALLSELECTED ( Dates[Month Short] ) ) < COUNTROWS ( ALL ( Dates[Month Short] ) )  
RETURN
    --will return  the full last year if year if no period is selected
    IF ( MonthSelectedTest, LastYear, FullYear )

Attached is a sample pbix

[Ikigai]

Thank you so much, danextian!

This worked perfectly.  I appreciate your help and assistance.

[rajendraongole1]

Hi @Ikigai -  you can modify your measure

Prior Year Average =
IF (
ISFILTERED('Date Table'[Month Name]),
-- Case 1: If months are filtered, use DATEADD for respective prior year months
CALCULATE(Customers[Average], DATEADD('Date Table'[Date], -1, YEAR)),
-- Case 2: If no specific month filter, calculate the full prior year average
CALCULATE(
Customers[Average],
DATESBETWEEN(
'Date Table'[Date],
DATEADD(STARTOFYEAR('Date Table'[Date]), -1, YEAR),
DATEADD(ENDOFYEAR('Date Table'[Date]), -1, YEAR)
)
)
)

Try the above measure.It retains the ability to calculate specific prior year months when they are selected.
It ensures that the entire prior year is used for calculations when the full year is selected or nothing is selected.

[Ikigai]

Hello rajendraongole1,

Thank you so much for your response and assistance.

I inputted the modified measure you provided, but unfortunately, I am still getting the same result in the Example#2 screenshot--still able to get prior month averages when selected, but still not getting 2023 full year average when slicer isn't selected or 2024 is checked.

I double-checked to make sure I entered the modified measure exactly, but still get the same result.