Forum Discussion

Sunilrane's avatar
Sunilrane
New Member
4 years ago
Solved

How to calculate % Change

Hi everyone,

I need some help to calculate percentage change of the following example pasted below

 

[All Periods][$]
CYTD Wks - 5   02/05/22$1,452,541,355
CYTD Wks PY - 5    02/05/21$77,139,973
Latest 13 Wks -   02/05/22$3,545,800,901
Latest 13 Wks PY -   02/05/21$173,655,357
Latest 4 Wks -   02/05/22$1,052,303,407
Latest 4 Wks PY-   02/05/21$56,642,000
Latest 52 Wks -   02/05/22$8,767,361,949
Latest 52 Wks PY -   02/05/21$414,221,751

 

Thanks and Regards, 

Sunil Rane

  • v-jinweili-msft's avatar
    v-jinweili-msft
    4 years ago

    Hi Sunilrane,

     

    It seems that there are only two columns in your table:

    So you may create two columns to extract the Date and the Type:

    Date =
RIGHT ( [[All Periods]]], 8 )
    Type =
VAR _findWks =
    FIND ( "Wks", [[All Periods]]] )
RETURN
    LEFT ( [[All Periods]]], _findWks + 2 )

     

    Output:

     

    Then create a measure 

    Changed Percentage =
VAR _current =
    SUM ( 'Table'[[$]]] )
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[[$]]] ),
        FILTER (
            ALL ( 'Table' ),
            [Type] = MAX ( 'Table'[Type] )
                && YEAR ( [Date] )
                    = YEAR ( MAX ( 'Table'[Date] ) ) - 1
        )
    )
RETURN
    DIVIDE ( _previous, ( _current - _previous ) )

    And change the format to Percentage

     

    The final output:

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

4 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    4 years ago

    Sunilrane If you have a Table named Tabella 5 like this:

    you can calculate the percentage change between the same period of the two years with this measure:

    Misura 2 =
    VAR MAXD = CALCULATE(MAX('Tabella (5)'[Date]))
    VAR SUMMAX = CALCULATE(SUM('Tabella (5)'[[$]]]),'Tabella (5)'[Date] = MAXD)
    VAR PREVYE = CALCULATE(SAMEPERIODLASTYEAR('Tabella (5)'[Date]))
    VAR SUMPREV = CALCULATE(SUM('Tabella (5)'[[$]]]),'Tabella (5)'[Date] = PREVYE)
    RETURN SUMMAX - PREVYE
     
    BF
  • v-jinweili-msft's avatar
    v-jinweili-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Hi Sunilrane,

     

    It seems that there are only two columns in your table:

    So you may create two columns to extract the Date and the Type:

    Date =
RIGHT ( [[All Periods]]], 8 )
    Type =
VAR _findWks =
    FIND ( "Wks", [[All Periods]]] )
RETURN
    LEFT ( [[All Periods]]], _findWks + 2 )

     

    Output:

     

    Then create a measure 

    Changed Percentage =
VAR _current =
    SUM ( 'Table'[[$]]] )
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[[$]]] ),
        FILTER (
            ALL ( 'Table' ),
            [Type] = MAX ( 'Table'[Type] )
                && YEAR ( [Date] )
                    = YEAR ( MAX ( 'Table'[Date] ) ) - 1
        )
    )
RETURN
    DIVIDE ( _previous, ( _current - _previous ) )

    And change the format to Percentage

     

    The final output:

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