Forum Discussion

jhauw74's avatar
jhauw74
Icon for Helper I rankHelper I
1 year ago
Solved

Calculating YoY DAX

Hi Everyone - wishing you all to be well !! I want to know how to calculate YoY based on this scenario: The year on year % needs to be calculated based on: 1. FY 2023 performance which...
Need Help
  • jhauw74's avatar
    jhauw74
    1 year ago

    Hi Everyone - thanking you all for sharing the ideas on how to solve this issue.

    I am managed reconfigure the logic in the DAX by using the following:

    YoY Growth FY =
    VAR CurrentWeekEnd = MAX('BOM_DIM_Date'[Week_Ending_Date])
    VAR CurrentFYStart = DATE(YEAR(CurrentWeekEnd) - IF(MONTH(CurrentWeekEnd) < 7, 1, 0), 7, 1)
    VAR PreviousFYStart = DATE(YEAR(CurrentFYStart) - 1, 7, 1)
    VAR CurrentFYValue = CALCULATE([FixedActivationsAll], 'BOM_DIM_Date'[Calendar_Date] >= CurrentFYStart, 'BOM_DIM_Date'[Calendar_Date] <= CurrentWeekEnd)
    VAR PreviousFYValue = CALCULATE([FixedActivationsAll], 'BOM_DIM_Date'[Calendar_Date] >= PreviousFYStart, 'BOM_DIM_Date'[Calendar_Date] <= EDATE(CurrentWeekEnd, -12))
    RETURN
    DIVIDE(CurrentFYValue - PreviousFYValue, PreviousFYValue, 0)

    Note:
    BOM_DIM_Date - calendar table
    FixedActivationsAll - my measure
audreygerred's avatar
audreygerred
Icon for Super User rankSuper User
1 year ago

Instead of having weekending date in your columns you will need to put in the week number. When you put days in your visual, the YoY will look at that date of the prior year. Use the week number instead, then week 1 of 2024 will look at week 1 of 2023. If ppl are unsure of what the weekending date is for any given week number, you can add a legend somewhere in your report.