Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sraanvi
Frequent Visitor

Calculating month on month growth % with dynamic slicers

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#EngagedMonth
Atlantic Coast450August
Northeast345August
Southeast368August
West543August
Atlantic Coast435September
Northeast350September
Southeast376September
West548September
Atlantic Coast445October
Northeast346October
Southeast365October
West542October

 

Thanks

Sandy

8 REPLIES 8

Eg1.PNG

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.

 

Hi Vipul,

 

Thanks for this.

 

What does the 

'Table'[M1]

indicate? 

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.

Capture.PNG

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

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.S1.PNG

 

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.

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

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.


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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.