How to fix DAX measures to provide dates for prior year?

[ERing]

I'm been handed a dashboard which is currently not providing an accurate output and I'm trying to determine where the errors in the DAX are.

The report has a slicer where the user selects a date range and budget, actual, and % vs budget data is supposed to be shown for the current period (Range), QTD, & YTD.

I believe I've identified an issue with the DAX below.

The Prev Range Spend measure uses variables for Prev_Year_Start and Prev_Year_End. It appears the DAX for Selected_Prev_Year_Start will provide the MIN of the dates in my DIM_CALENDAR[Prev_Year_Start] column. This will be the January 1st of the previous year. Similarly it appears the DAX for Selected_Prev_Year_End will provide the MIN of dates in my DIM_CALENDAR[Prev_Year_End]. This will be December 31st of the previous year.

Instead what I want is the DAX for Selected_Prev_Year_Start and Selected_Prev_Year_End to provide the same dates selected in the slicer but for one year prior.

How can I Selected_Prev_Year_Start and Selected_Prev_Year_End so that they provide the same dates in the slicer but for one year prior?

Prev Range Spend = 
VAR SelectedDate = SELECTEDVALUE('DIM_CALENDAR'[DATE]) 

VAR Prev_year_Start = 
[Selected_Prev_Year_Start]

VAR Prev_year_End = 
[Selected_Prev_Year_End]

RETURN  
CALCULATE(
    SUM(FACT_SPEND[SPEND]), 
    'DIM_CALENDAR'[DATE] >= Prev_year_Start &&
    'DIM_CALENDAR'[DATE] <= Prev_year_End
)

Selected_Prev_Year_Start = 
CALCULATE(
    MIN('DIM_CALENDAR'[Prev_Year_Start]), 
    ALLSELECTED('DIM_CALENDAR')
)

Selected_Prev_Year_End = 
CALCULATE(
    MIN('DIM_CALENDAR'[Prev_Year_End]), 
    ALLSELECTED('DIM_CALENDAR')
)

[v-saisrao-msft]

Hi @ERing,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[danextian · Accepted solution]

Hi @ERing 

You can use EDATE to get the date months back. For example

EDATE ( [Selected_Prev_Year_Start], -12 )

Or you can use SAMEPERIODLASTYEAR

CALCULATE (
    [your measure],
    SAMEPERIODLASTYEAR ( Dates[Date] ),
    REMOVEFILTERS ( Dates )
)

REMOVEFILTERS is necessary is the Dates table hasn't been marked as a dates table.

[ERing]

@danextian 

I don't believe this solves my problem. I need a way to create Selected_Prev_Year_Start so that its the slicer selection minus one year and a way to create Selected_Prev_Year_End so that its the slicer selection minus one year. 

[v-saisrao-msft]

Hi @ERing,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @ERing,

Thank you for reaching out to the Microsoft Fabric Forum community.

I understand your challenge—you need the prior-year dates (Selected_Prev_Year_Start and Selected_Prev_Year_End) to dynamically match the slicer’s selected range but shifted back by one year (e.g., March 10–16, 2025 → March 10–16, 2024). Here’s how to fix it: 

• Selected_Prev_Year_Start = Slicer’s start date minus 1 year. 

• Selected_Prev_Year_End = Slicer’s end date minus 1 year. 

• Use MIN and MAX on your date column to dynamically grab the slicer’s selected range. 

• Apply a year-shift function (like DATEADD or EDATE) to subtract 1 year from those dates. 

• Leap Years: Ensure February 29, 2024, correctly maps to February 28, 2023. 

• Partial Ranges: Confirm dates like December 25–31, 2025, compared to December 25–31, 2024. 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.