Measures for Customer Trends

Question

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 what is the raw data while the output is the desired measures. Also with numerous different Customers like Cust_1, Cust_2…… should the measures hold per customer.Table 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Input&nbsp;&nbsp;&nbsp;Output&nbsp;&nbsp;DateNameVol&nbsp;% MoM% YoY% of 3 month averageJan-16Cust_110&nbsp;&nbsp;&nbsp;&nbsp;Feb-16Cust_112&nbsp;20.0%&nbsp;&nbsp;Mar-16Cust_114&nbsp;16.7%&nbsp;116.7%Apr-16Cust_18&nbsp;-42.9%&nbsp;70.6%May-16Cust_115&nbsp;87.5%&nbsp;121.6%Jun-16Cust_113.6&nbsp;-9.3%&nbsp;111.5%Jul-16Cust_114.2&nbsp;4.4%&nbsp;99.5%Aug-16Cust_114.8&nbsp;4.2%&nbsp;104.2%Sep-16Cust_115.4&nbsp;4.1%&nbsp;104.1%Oct-16Cust_116&nbsp;3.9%&nbsp;103.9%Nov-16Cust_116.6&nbsp;3.8%&nbsp;103.8%Dec-16Cust_117.2&nbsp;3.6%&nbsp;103.6%Jan-17Cust_117.8&nbsp;3.5%78.0%103.5%Feb-17Cust_118.4&nbsp;3.4%53.3%103.4%Mar-17Cust_119&nbsp;3.3%35.7%103.3%Apr-17Cust_119.6&nbsp;3.2%145.0%103.2%May-17Cust_120.2&nbsp;3.1%34.7%103.1%Jun-17Cust_120.8&nbsp;3.0%52.9%103.0%Jul-17Cust_121.4&nbsp;2.9%50.7%102.9%Aug-17Cust_122&nbsp;2.8%48.6%102.8%Sep-17Cust_122.6&nbsp;2.7%46.8%102.7%Oct-17Cust_123.2&nbsp;2.7%45.0%102.7%Nov-17Cust_123.8&nbsp;2.6%43.4%102.6%Dec-17Cust_124.4&nbsp;2.5%41.9%102.5%&nbsp;Any assistance will he highly appreciated.

zubair_muhammad · Answer

Hi norbi&nbsp;Try this Measure for MoM&nbsp;MoM =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        SUM ( TableName[Vol] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            TableName[Date] = PREVIOUSMONTH ( VALUES ( TableName[Date] ) )
        )
    )
)
    - 1

zubair_muhammad · Answer

norbi&nbsp;This Measure for YoY&nbsp;YoY =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        SUM ( TableName[Vol] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Name] ),
            YEAR ( TableName[Date] )
                = YEAR ( SELECTEDVALUE ( TableName[Date] ) ) - 1
                &amp;&amp; MONTH ( TableName[Date] ) = MONTH ( SELECTEDVALUE ( TableName[Date] ) )
        )
    )
)
    - 1

zubair_muhammad · Answer

norbi&nbsp;Final one&nbsp;% of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)

zubair_muhammad · Answer

norbi&nbsp;File attached here with your sample data

norbi · Answer

Hi&nbsp;Zubair_Muhammad,&nbsp;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.DateNameVolJan-16Cust_13Jan-16Cust_24Jan-16Cust_31Jan-16Cust_42Feb-16Cust_12Feb-16Cust_21Feb-16Cust_33Feb-16Cust_46…..&nbsp;&nbsp;

Forum Discussion

Measures for Customer Trends

10 Replies

Related content

calculate trend

Graphing trending

Measures vs Custom Columns

Customer Scorecard

Scatter chart - customize trend line

Table 1
Input			Output
Date	Name	Vol	% MoM	% YoY	% of 3 month average
Jan-16	Cust_1	10
Feb-16	Cust_1	12	20.0%
Mar-16	Cust_1	14	16.7%		116.7%
Apr-16	Cust_1	8	-42.9%		70.6%
May-16	Cust_1	15	87.5%		121.6%
Jun-16	Cust_1	13.6	-9.3%		111.5%
Jul-16	Cust_1	14.2	4.4%		99.5%
Aug-16	Cust_1	14.8	4.2%		104.2%
Sep-16	Cust_1	15.4	4.1%		104.1%
Oct-16	Cust_1	16	3.9%		103.9%
Nov-16	Cust_1	16.6	3.8%		103.8%
Dec-16	Cust_1	17.2	3.6%		103.6%
Jan-17	Cust_1	17.8	3.5%	78.0%	103.5%
Feb-17	Cust_1	18.4	3.4%	53.3%	103.4%
Mar-17	Cust_1	19	3.3%	35.7%	103.3%
Apr-17	Cust_1	19.6	3.2%	145.0%	103.2%
May-17	Cust_1	20.2	3.1%	34.7%	103.1%
Jun-17	Cust_1	20.8	3.0%	52.9%	103.0%
Jul-17	Cust_1	21.4	2.9%	50.7%	102.9%
Aug-17	Cust_1	22	2.8%	48.6%	102.8%
Sep-17	Cust_1	22.6	2.7%	46.8%	102.7%
Oct-17	Cust_1	23.2	2.7%	45.0%	102.7%
Nov-17	Cust_1	23.8	2.6%	43.4%	102.6%
Dec-17	Cust_1	24.4	2.5%	41.9%	102.5%