Help! Dynamic Year over Year Calculation required

[georgian4344]

I have a measure called Month Volume, and my goal is to calculate the year-over-year percentage difference in month volume, but with a specific condition.

Typically, this can be accomplished using functions like SamePeriodLastYear, ParallelPeriod, or DateAdd, which I've already employed here:

DAX used:

Month Volume YoY% =

VAR __PREV_YEAR = CALCULATE([Month Volume], PARALLELPERIOD('Date Table'[Date], -12, MONTH), ALL('Date Table'))

    RETURN

        DIVIDE([Month Volume] - __PREV_YEAR, __PREV_YEAR)

However, the condition I aim to implement differs from the static calculation approach where it compares Jan 2024 with Jan 2023, Feb 2024 with Feb 2023, and so forth. Instead, I need the calculation to dynamically adjust when a date filter is applied. For instance, if the filter starts from Feb 2023, I want it to compare Jan 2024 with Feb 2023, Feb 2024 with March 2023, and continue in this manner as illustrated below:

How can I achieve this?

Notes to consider:

• Month Volume =
  VAR _ShipDateDiff = DATEDIFF(MIN('TMR Query'[Ship Date]), MAX('TMR Query'[Ship Date]), DAY) + 1
  VAR _RESULT = (more dax)
  RETURN _RESULT
• 

[mark_endicott]

@georgian4344 - Sorry, I wouldn't encourage this comparison due to seasonality and the number of days in a month. Comparing the 28 days of February with the 31 of May - regardless of year - isn't fair, and that's before we factor in seasonal factors (for example comparing ice cream sales in February and June isnt fair).

I hope you find the answer, but I wont be providing it.   

[georgian4344 · Accepted solution]

I understand, and I appreciate your help! It was a requirement from my project, so I had to find a way to implement it. I managed to figure it out myself, and here's how I did it:

Previous Year Month Volume =

VAR _OFFSET  =

    VAR MinDateInContext = CALCULATE(MIN('Date Table'[Date]), ALLSELECTED('Date Table'))

    RETURN MONTH(MinDateInContext)

VAR _CONDITION = YEAR(CALCULATE(MIN('Date Table'[Date]), DATEADD('Date Table'[Date], -13 + _OFFSET, MONTH)))

VAR _CURRENTYEAR = YEAR(MAX('Date Table'[Date]))

VAR _PREVYEAR = _CURRENTYEAR - 1

VAR RESULT =

    IF(_CURRENTYEAR && _CONDITION = _PREVYEAR, CALCULATE([Month Volume], DATEADD('Date Table'[Date], -13 + _OFFSET, MONTH)), BLANK())

RETURN RESULT

YOY% = DIVIDE([Month Volume] - [Previous Year Month Volume], [Previous Year Month Volume])

[mark_endicott]

@georgian4344 - I would always advise pushing back on these kinds of requirements, they're setting up false comparisons and anyone working with data should feel empowered to advise that a requirement is going to potentially lead to poor decisions. 

[mark_endicott]

@georgian4344 - You can try this:

Month Volume YoY% =
VAR __PREV_YEAR = CALCULATE([Month Volume], DATEADD('Date Table'[Date], -11, MONTH))
    
RETURN
        DIVIDE([Month Volume] - __PREV_YEAR, __PREV_YEAR)

This will compare with a month offset i.e Jan 24 to Feb 23 etc. 

If this works, please accept as the solution. 

[georgian4344]

Thanks for the response Mark,

Unfortunately, this isn't quite the solution I was looking for. While it works for the specific condition where the date starts from Feb 2023, it won't dynamically adapt if the date filter starts from March 2023, April 2023, or any other month. I need a solution that adjusts dynamically based on the starting date of the filter.

[mark_endicott]

@georgian4344 - what would you be expecting the calculation to be comparing against if the date filter starts from March 2023 & April 2023?

DATEADD is dynamic, so with the -11, MONTH - March 2023 would compare against April 2022

It's unclear what you are looking for if not this. 

[georgian4344]

Thanks for your follow up Mark,

I understand that DATEADD with -11 months is dynamic, but it doesn't match the comparison I'm aiming for. If the date filter starts from March 2023, I don't want it to compare March 2023 with April 2022. Instead, I want the comparison to always start with January 2024, then February 2024, and so on.

Here's what I mean:

Filter Start Date	Comparison	Calculation
Jan 2023	Jan 2024	Jan 2024 vs Jan 2023
	Feb 2024	Feb 2024 vs Feb 2023
	...	...
March 2023	Jan 2024	Jan 2024 vs March 2023
	Feb 2024	Feb 2024 vs April 2023
	...	...
April 2023	Jan 2024	Jan 2024 vs April 2023
	Feb 2024	Feb 2024 vs May 2023
	...	...

This way, regardless of the starting month of the date filter, the comparisons always begin with January 2024, followed by February 2024, and continue in that sequence.

I hope this clarifies, and again really appreciate your help