Forum Discussion
DATEADD function (DAX) for previous year by slicer
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
Super UserHi 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.
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.
