Measures for Customer Trends

[norbi]

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
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%

Any assistance will he highly appreciated.

[Zubair_Muhammad]

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]

@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]

@norbi

Final one

% of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)

[Zubair_Muhammad]

@norbi

File attached here with your sample data

[norbi]

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
…..

[Zubair_Muhammad]

Hi @norbi

Just Replace

ALLEXCEPT ( TableName, TableName[Name] )

With

ALL (TableName)

[norbi]

Hi @Zubair_Muhammad,

How would the average work for the three month average as numerous Cust_* need to be added together and then averaged per month?

% of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)

[Zubair_Muhammad]

HI @norbi

See the Addition in Red font

% of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        ALL ( TableName ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)

[norbi]

Hi @Zubair_Muhammad

Thank you or the feedback. When I try the formula with multiple entries for the month it averages every value and over three months. I would like to sum the entire month of data for all customer and the average only on the monthly totals for 3 months? Your assistance will be highly appreciated.

[Zubair_Muhammad]

Hi Norbi,

Could you paste some data with multiple customers and multiple dates and result you expect?