Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Help! Dynamic Year over Year Calculation required

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 f...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
mark_endicott
Super User
2 years ago

Anonymous - 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. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 DateComparisonCalculation
Jan 2023Jan 2024Jan 2024 vs Jan 2023
 Feb 2024Feb 2024 vs Feb 2023
 ......
March 2023Jan 2024Jan 2024 vs March 2023
 Feb 2024Feb 2024 vs April 2023
 ......
April 2023Jan 2024

Jan 2024 vs April 2023

 Feb 2024Feb 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