cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sraanvi Frequent Visitor
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
VipulToshniwal Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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.

 

sraanvi Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

Hi Vipul,

 

Thanks for this.

 

What does the 

'Table'[M1]

indicate? 

VipulToshniwal Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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

M1 = Sum('Table'[#Engaged])
sraanvi Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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

VipulToshniwal Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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.

sraanvi Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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 Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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 Frequent Visitor
Frequent Visitor

Re: Calculating month on month growth % with dynamic slicers

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 184 members 1,776 guests
Please welcome our newest community members: