Forum Discussion

gopu99's avatar
gopu99
Frequent Visitor
3 years ago
Solved

YOY Difference help

Hi i have a data which will be refreshed timely

Tuition account Year
50texas2020-2021
100texas2021-2022
150texas2022-2023
200penstate2023-2024
250penstate2024-2025
300perdue2025-2026
350perdue2026-2027
400perdue2027-2028

i want to calculate the year on year change in Tuition Fee based on the Account 
i tried to create a date table but it didnt work 

Currently trying with this DAX Formula

YoY Growth =
VAR CurrentYearFee = SUM(Sheet1[Tuition ])
VAR PreviousYearFee =
    CALCULATE(
        SUM(Sheet1[Tuition ]),
        FILTER(
            ALL(Sheet1), (VALUE(Sheet1[ Year]) - 1)
        )
    )
RETURN
IF(ISBLANK(PreviousYearFee), 0, (CurrentYearFee - PreviousYearFee) / PreviousYearFee)
when i use this formul it is returning all 0 in all the rows
Please let me know any changes or things i can do to solve this thank you.

General Comment
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi gopu99 ,

    You can update the formula of measure [YoY Growth] as below, and check if that is what you want.

    YoY Growth = 
VAR _selaccount =
    SELECTEDVALUE ( 'Sheet1'[account] )
VAR _selyear =
    SELECTEDVALUE ( 'Sheet1'[Year] )
VAR CurrentYearFee =
    SUM ( Sheet1[Tuition] )
VAR _preyear =
    CALCULATE (
          MAX ( Sheet1[Year] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            'Sheet1'[account] = _selaccount
                && 'Sheet1'[Year] < _selyear
        )
    )
VAR PreviousYearFee =
    CALCULATE (
        SUM ( Sheet1[Tuition] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            'Sheet1'[account] = _selaccount
                && 'Sheet1'[Year] =_preyear
        )
    )
RETURN 
    IF (
        ISBLANK ( PreviousYearFee ),
        BLANK (),
        ( CurrentYearFee - PreviousYearFee ) / PreviousYearFee
    )

    Best Regards