Forum Discussion

jhauw74's avatar
jhauw74
Helper 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 start on 1/07/2023 - this needs to be calculated from 1/07/2023 through to 27/09/2023

2. FY 2024 performance which start on 1/07/2024 - this needs to be calculated from 1/07/2024 through to 27/09/2024

I have used the DAX formula 'same period as last year' however is resulting the calculation is comparing FY 23 vs FY 24 period  in days ie. from 20/09/24 - 27/09/24 vs 20/09/23 - 27/09/23 as this is based on Week Ending Friday in the calendar table.

Thank you in advance for any assistance 🙂

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

4 Replies

  • jhauw74's avatar
    jhauw74
    Helper I
    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
    Super 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, jhauw74 

    Thanks for audreygerred's reply. You can refer to audreygerred's advice or you can share the pbix file without sensitive data for testing purposes. Feel free to help you.

    Best Regards,
    Yang
    Community Support Team