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])
Anonymous's avatar
Anonymous
Not applicable
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 - 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.