Forum Discussion

norbi's avatar
norbi
Icon for Helper I rankHelper I
8 years ago

Measures for Customer Trends

Hi, I would appreciate assistance in solving the percentage Month on Month (MoM) growth, percentage Year on Year (YoY) growth and percentage of 3 month average. Below is the table where the input is...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi norbi

 

Try this Measure for MoM

 

MoM =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        SUM ( TableName[Vol] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[Date] = PREVIOUSMONTH ( VALUES ( TableName[Date] ) )
        )
    )
)
    - 1
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

norbi

 

This Measure for YoY

 

YoY =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        SUM ( TableName[Vol] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            YEAR ( TableName[Date] )
                = YEAR ( SELECTEDVALUE ( TableName[Date] ) ) - 1
                && MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) )
        )
    )
)
    - 1
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    norbi

     

    Final one

     

    % of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)
      • norbi's avatar
        norbi
        Icon for Helper I rankHelper I
        8 years ago

        Hi Zubair_Muhammad,

         

        Thank you for the feedback it works well, however how would you achieve the sum of the Customers if there are more than one in a month? E.g.

        Date

        Name

        Vol

        Jan-16

        Cust_1

        3

        Jan-16

        Cust_2

        4

        Jan-16

        Cust_3

        1

        Jan-16

        Cust_4

        2

        Feb-16

        Cust_1

        2

        Feb-16

        Cust_2

        1

        Feb-16

        Cust_3

        3

        Feb-16

        Cust_4

        6

        …..