Calculating month on month growth % with dynamic slicers

[sraanvi]

Hello,

I have a data set sample as below: 

I need to calculate the month on month growth% based on the #engaged value. The month would be on a slicer, such that whenever i click on october, i need the growth% as ((#engaged october)-(#engaged September))/(#engaged September).

Kindly help me with this

Region	#Engaged	Month
Atlantic Coast	450	August
Northeast	345	August
Southeast	368	August
West	543	August
Atlantic Coast	435	September
Northeast	350	September
Southeast	376	September
West	548	September
Atlantic Coast	445	October
Northeast	346	October
Southeast	365	October
West	542	October

Thanks

Sandy

[VipulToshniwal]

Hi, 

Use the following steps to build the visual that you need.

1) Get your Month column into a proper date format. I added a  calculated column using the SWITCH function for ease.

M = 
SWITCH (
    'Table'[Month],
    "August", "08/ 01 / 2019",
    "September", "09 / 01 / 2019",
    "October", "10 / 01 / 2019"
)

2) Create an MoM measure. 

MoM = (sum('Table'[#Engaged])-CALCULATE(Sum('Table'[#Engaged]),PREVIOUSMONTH('Table'[M].[Date])))/CALCULATE(Sum('Table'[#Engaged]),PREVIOUSMONTH('Table'[M].[Date]))

3) Format the MoM measure using the modeling tab into a percentage dataset. 

4) Build visuals and slicers.

[sraanvi]

Hi Vipul,

Thanks for this.

What does the 

'Table'[M1]

indicate? 

[sraanvi]

Hi Vipul,

Thanks for your suggestions. However, I am getting the growth% as 100% or infinity. 

Please check the  formulaes below for the attached changes I had made.

1)changing date format- 

Date format = SWITCH('LCR data'[Month],"August","08/01/2019","September","09/01/2019","October","10/01/2019")

2) Growth measure

MOM growth% = (sum('LCR data'[#Engaged])-CALCULATE(Sum('LCR data'[#Engaged]),PREVIOUSMONTH('LCR data'[Date format].[Date])))/CALCULATE(Sum('LCR data'[#Engaged]),PREVIOUSMONTH('LCR data'[Date format].[Date]))

Please find the snapshot of output attached.

Kindly help on the same

[VipulToshniwal]

Hi sraanvi,

I recommend that you break the formula into measures so that you can check if they are working properly or not. 

Build measures like

M1 = sum('LCR data'[#Engaged])

M2 =
CALCULATE (
    SUM ( 'LCR data'[#Engaged] ),
    PREVIOUSMONTH ( 'LCR data'[Date format].[Date] )
)

The measure M2 should give you the sum of the engaged column till that last month in the context of the selected date.

For ex. when I choose october, M1 shows total in october, but M2 shows total in september.

If, this works fine, use the following formula to create MOM growth%

MOM growth% = ([M1]-[M2])/[M2]

Now, just format MOM growth% under the modeling tab with a click on % sign, and the measure will display values as a percentage.

Also, mark the msg as the solution if this works. It will help others to identify the right step.

[sraanvi]

Hi Vipul,

I could understand that the issue arises with the formulae M2. its appearing as a blank. I tried using 2-3 datasets...

Would we need any 'if' condition for the BI to identify the current month we have selected in the filter, such that it can calculate the previous month

[VipulToshniwal]

Hi,

I think the issue you are facing is not because the DAX is not working, but it is because your 'Date Format' column might not be in a date-time format accepted by PowerBI. 
PREVIOUSMONTH is a time intelligent function that needs the input in a recognizable date-time format. 
Visit the data modeling tab to make sure that your 'Date Format' column is formatted to date-time.

[sraanvi]

i have corrected the data type to "Date/Time". Do we need to change the format too?

[VipulToshniwal]

Hi. I have updated the code to eliminate M1.  It was a test measure created using.

M1 = Sum('Table'[#Engaged])