How to Calculate Monthly Sales Growth rate using Power BI DAX

How to show Current Month vs Previous Month Sales Growth Rate using Power BI DAX

Scenario:

Suppose we have a Date Model as follows:

The Relations are as follows:

Now from the above Model, we need to calculate the Monthly Sales comparison between the Current Month and Previous Month with below requirement:

1) If  there is No selection on Year and Month, then we need to show the Sales of the Current in the Current Year.

2) If  there is a selection on Year and Month, then we need to show the Sales of the selected Year or Month.

------------------------------------------------------------------------------------

To fulfill the above requirement, we need to create the following Calculated Columns on the Date Table (tbl_Calendar) as discussed below:

The below DateFilter Flag will be helpful to filter out extra dates that flowing from tbl_Calendar table, for which no Sales in the fact table:

DateFilterFlag =

    VAR vMinFlag = tbl_Calendar[cDate]>=MIN(tbl_OrderDetails[Order_Date])

    VAR vMaxFlag = tbl_Calendar[cDate]<=MAX(tbl_OrderDetails[Order_Date])

 RETURN

   IF( AND(vMinFlag=TRUE(), vMaxFlag=TRUE()), 1,0)

The below Current Month Flag will be helpful to filter the Current Month in Current Year while doing the calculations:

Cur Month Flag =

IF(

        YEAR(tbl_Calendar[cDate])=YEAR(TODAY())&&

        MONTH(tbl_Calendar[cDate])=MONTH(TODAY()),

        1,0)

The below Current Year Flag will be helpful to filter the Current Year while doing calculations:

Cur Year Flag = IF( MONTH(tbl_Calendar[cDate])=YEAR(TODAY()),1,0)

------------------------------------------------------------------------------------

To fulfill the above requirement, we need to create the following Measures on the above Model as discussed below:

Net Sales =

SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

------------------------------------------------------------------------------------

The dynamic Measures to show the sales of Current Month, Previous Month, and Delta of the Current Month vs Previous Month, which will be shown on the Card visuals:

The below Measures by default will show the Sales of Current of Month of Current Year when no Year or Month is selected in the Slicers.

Current Month Sales =

VAR vNetSales = [Net Sales]

VAR vFilterFlag = OR(ISFILTERED(tbl_Calendar[Year]),

                                    ISFILTERED(tbl_Calendar[Month_Name])

                                    )

VAR vCurMonthSales =

CALCULATE(

        [Net Sales],

        tbl_Calendar[Cur Month Flag]=1)

RETURN

IF( vFilterFlag=TRUE(),

        vNetSales, vCurMonthSales

        )

Previous Month Sales =

VAR vFilterFlag = OR(ISFILTERED(tbl_Calendar[Year]),

                                    ISFILTERED(tbl_Calendar[Month_Name])

                                    )

VAR vPrevSales =

CALCULATE (

        [Net Sales],

        DATEADD( tbl_Calendar[cDate], -1, MONTH )

        )

VAR vPrevMonthSales =

CALCULATE(

        [Net Sales],

        DATEADD(CALCULATETABLE(DISTINCT(tbl_Calendar[cDate]),

                            tbl_Calendar[Cur Month Flag]=1),-1, MONTH)

        )

RETURN

IF( vFilterFlag=TRUE(),

        vPrevSales, vPrevMonthSales

        )

Cur Month vs Prev Month =

[Current Month Sales]-[Previous Month Sales]

% Cur Month vs Prev Month =

DIVIDE(([Current Month Sales]-[Previous Month Sales]),

                                        [Previous Month Sales])

------------------------------------------------------------------------------------

The Measures to show the sales of Current Month, Previous Month, and Delta of the Current Month vs Previous Month, which will be shown on the Table visual:

The below Measures will show the sales of Current Month, Previous Month, and Delta of the Current Month vs Previous Month, when the Month field is in the Filter Context of the Visual.

Cur Month Sales =

CALCULATE([Net Sales], tbl_OrderDetails[Order_Date]<>BLANK())

Prev Month Sales =

    CALCULATE (

        [Cur Month Sales],

        DATEADD( 'tbl_Calendar'[cDate], -1, MONTH )

    )

Var of Monthly Sales =

[Cur Month Sales]-[Prev Month Sales]

% Var of Monthly Sales =

VAR vRatio = DIVIDE(([Cur Month Sales]-[Prev Month Sales]),

                                        [Prev Month Sales],BLANK()

                                    )

RETURN

SWITCH(TRUE(),

ISBLANK([Cur Month Sales]), BLANK(),

ISBLANK([Prev Month Sales]), 1,

CALCULATE(vRatio, tbl_OrderDetails[Order_Date]<> BLANK())

------------------------------------------------------------------------------------

Now we will see the result of all the above formulas as follows.

Case 1: Default view with no selection on Year and Month Slicers/Filters:

Case 2: The view with a selection on Year and Month Slicers/Filters:

------------------------------------------------------------------------------------

Notes:

In the above we have enabled the Rules for Growth Indicators for the [% Var of Monthly Sales] as per below:

------------------------------------------------------------------------------------
We can also create a Custom Icons using the UNICHAR() Function as showing below:

Growth Icon =

SWITCH ( TRUE(),

    [% Cur Month vs Prev Month]=BLANK(), UNICHAR(9632),

    [% Cur Month vs Prev Month]=1.00, UNICHAR(9632),

    [% Cur Month vs Prev Month]<0, UNICHAR (9660),

    [% Cur Month vs Prev Month]>0, UNICHAR (9650)  

)

Based on the above Icons, we can apply the Color for each Icon using the below logic:

Color Icon =

SWITCH ( [Growth Icon],

    UNICHAR(9632), "#666666", //Grey

    UNICHAR(9660),"#FD7F6F", //Red

    UNICHAR(9650), "#87BC4F" //Green

)

Result:

------------------------------------------------------------------------------------

Finally we will build a Combo Chart ( Line and Clustered Column Chart) as follows:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------