Month on Month Growth percentage calculation based on Month Number/Month Name using DAX

[Charu]

Dear Community members,

Hope all are doing well!

There is a scenario where we need to compute month-on-month growth percentage calculation based on the Month number or month name in the data.

Please Help me with the possibilities in Power BI.

Sample data is here for your reference

MonthNum |  MonthName | Revenue

                 1 |              Jan    |          100

                 2 |              Feb   |           120

                 3 |              Mar  |            150

Expected output:

MonthNum |  MonthName | Revenue     | Growth %

                 1 |              Jan    |          100    |

                 2 |              Feb   |          120    |  20%

                 3 |              Mar  |           150   |   30%

Growth % is the simple calculation that is current month revenue- previous month revenue.

This dataset doesn't contain any date values that's the challenging part here. Kindly please help me out on this

Thanks and Regards,

Charu

[AllisonKennedy · Accepted solution]

@Charu , even though the dataset does not have date values, create a DateKey column and also create a DimDate table. Both are explained how to in my blog here: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

For you to create the DateKey column, you'll need to do it manually. You can merge Year and Month Number and 01 to get the date key required. 

Now just use simple time intelligence to calculate

Revenue = SUM(Revenue)

Prev Month Revenue = CALCULATE( [Revenue], DATEADD(DimDate[Date], -1, Month) )

Growth % = DIVIDE ( [Revenue - [Prev Month Revenue], [Prev Month Revenue])

[v-lionel-msft · Accepted solution]

Hi @Charu ,

Please try the formula.

Measure = 
VAR x = 
CALCULATE(
    MAX('Table (2)'[Revenue]),
    FILTER(
        ALLSELECTED('Table (2)'),
        'Table (2)'[MonthNum] = MAX('Table (2)'[MonthNum]) - 1
    )
)
RETURN
CALCULATE(
    DIVIDE(
        MAX('Table (2)'[Revenue]) - x,
        100
    ),
    FILTER(
        'Table (2)',
        x <> BLANK()
    )
)

Note: 

But there is a problem here. Since you don't have a year column, if you have multiple repeated months, how do you want to calculate it?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Charu]

Hi @v-lionel-msft ,

Foremost thank you for your DAX code it works as expected. And Yes, you are correct without the Date key field it's tough to analyze.  So planning for the data model with the date keys and will ease the DAX too.

Thank you for your valuable feedback

[AllisonKennedy · Accepted solution]

@Charu , even though the dataset does not have date values, create a DateKey column and also create a DimDate table. Both are explained how to in my blog here: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

For you to create the DateKey column, you'll need to do it manually. You can merge Year and Month Number and 01 to get the date key required. 

Now just use simple time intelligence to calculate

Revenue = SUM(Revenue)

Prev Month Revenue = CALCULATE( [Revenue], DATEADD(DimDate[Date], -1, Month) )

Growth % = DIVIDE ( [Revenue - [Prev Month Revenue], [Prev Month Revenue])

[Charu]

Hi @AllisonKennedy 

Like you said, going with the date keys in the data set is a great way to work on this time intelligence calculations. Thank you so much for your suggestion and timely help.