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

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.