Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX - Calculating % Difference from prior year based on Selected Year

Hi all,

 

I'm trying to replicate what is shown in here in the screenshot below for the % Diff field:

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a date table, assume your fiscal year is from March 1 to Feb next year, eg, Fiscal 2016 is from 2016/3/1~2017/2/28

    date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "fiscal year", IF (
        MONTH ( [Date] ) >= 3,
        YEAR ( [Date] ),
        YEAR ( [Date] ) - 1
    )
)

add calcualted columns

fiscal month = IF(MONTH([Date])>=3,MONTH([Date])-2,MONTH([Date])+10)

fiscal quarter =
SWITCH (
    TRUE (),
    [fiscal month] <= 3, "Q1",
    [fiscal month] <= 6, "Q2",
    [fiscal month] <= 9, "Q3",
    [fiscal month] <= 12, "Q4"
)

    Then create measures

    previous year'data = CALCULATE(SUM('Table'[Value]),SAMEPERIODLASTYEAR('date'[Date]))

difference = (SUM('Table'[Value])-[previous year'data])/[previous year'data]

value selected =
IF (
    ISINSCOPE ( 'Table'[actual/budget] ),
    FORMAT (
        SUM ( 'Table'[Value] ),
        "General number"
    ),
    FORMAT (
        [difference],
        "percent"
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This did not work. 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-juanli-msft thanks for letting me know, not sure how that happened.  Anyway, here's the screenshot.

       

      Basically what I'm trying to get to is to be able to replicate this screen above, where a user can choose a year and the %Diff column will show the growth % based on the selected year vs whatever the selected year's previous year is.

      Thanks, Gary

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous 

        Create a date table, assume your fiscal year is from March 1 to Feb next year, eg, Fiscal 2016 is from 2016/3/1~2017/2/28

        date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "fiscal year", IF (
        MONTH ( [Date] ) >= 3,
        YEAR ( [Date] ),
        YEAR ( [Date] ) - 1
    )
)

add calcualted columns

fiscal month = IF(MONTH([Date])>=3,MONTH([Date])-2,MONTH([Date])+10)

fiscal quarter =
SWITCH (
    TRUE (),
    [fiscal month] <= 3, "Q1",
    [fiscal month] <= 6, "Q2",
    [fiscal month] <= 9, "Q3",
    [fiscal month] <= 12, "Q4"
)

        Then create measures

        previous year'data = CALCULATE(SUM('Table'[Value]),SAMEPERIODLASTYEAR('date'[Date]))

difference = (SUM('Table'[Value])-[previous year'data])/[previous year'data]

value selected =
IF (
    ISINSCOPE ( 'Table'[actual/budget] ),
    FORMAT (
        SUM ( 'Table'[Value] ),
        "General number"
    ),
    FORMAT (
        [difference],
        "percent"
    )
)

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.