Power BI DAX: Group by Monthly Growth Rate-CodePudding

I got the following sample data:

Product Group   Product Monthly Start Date  Sales Qty
Mobile Phone A  Mobile Phone A-1    1/1/2021    100
Mobile Phone A  Mobile Phone A-1    2/1/2021    120
Mobile Phone B  Mobile Phone B-1    1/1/2021    90
Mobile Phone B  Mobile Phone B-1    2/1/2021    78

What I want is to calculate the Monthly product growth rate (below).

Product Group   Product Month Start Date    Growth Rate
Mobile Phone A  Mobile Phone A-1    1/1/2021    null
Mobile Phone A  Mobile Phone A-1    2/1/2021    20%
Mobile Phone B  Mobile Phone B-1    1/1/2021    null
Mobile Phone B  Mobile Phone B-1    2/1/2021    -13%

I guess I need to use groupby and sort order by the Month Start Date and calculate the rate.

Does anyone know the best way of calculating it?

Thanks.

CodePudding user response：

You would calculate month-over-month growth using a sequence of four measures:

sum of the column
amount for the prior month
change month-over-month
change month-over-month %

These DAX patterns can can be used to get you started. Make sure you add the necessary columns to your date table, then modify the measures below with your date table, fact table column to be quantified and measures you create.

DAX Patterns - Month Related Calculations

Sum of the Fact Table Column

Sales Amount:= sum ( SalesTable[Sales Qty] )

Amount for Prior Month

Sales PM := 
VAR CurrentYearMonthNumber = SELECTEDVALUE ( 'Date'[Year Month Number] ) 
VAR PreviousYearMonthNumber = CurrentYearMonthNumber - 1 
VAR Result =
        CALCULATE (
            [Sales Amount],
            REMOVEFILTERS ( 'Date' ),
            'Date'[Year Month Number] = PreviousYearMonthNumber
        ) 
RETURN
    Result

Change Month-Over-Month

Sales MOM :=
VAR ValueCurrentPeriod = [Sales Amount]
VAR ValuePreviousPeriod = [Sales PM]
VAR Result =
    IF (
        NOT ISBLANK ( ValueCurrentPeriod ) && NOT ISBLANK ( ValuePreviousPeriod ),
        ValueCurrentPeriod - ValuePreviousPeriod
    )
RETURN
    Result

Change Month-Over-Month %

Sales MOM % :=
DIVIDE (
    [Sales MOM],
    [Sales PM]
)

CodePudding user response：

I would do it this way (assuming you are viewing the result with a monthly granularity):

Total Sales Qty = SUM( ExampleTable[Sales Qty] )

MTD Sales Qty = TOTALMTD( [Total Sales Qty], Dates[Date] )

MTD Sales Qty LM = CALCULATE( [MTD Sales Qty], DATEADD(Dates[Date], -1, MONTH ) )

MoM Sales Qty Change = DIVIDE([MTD Sales Qty] - [MTD Sales Qty LM], [MTD Sales Qty LM], BLANK() )