Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
1)changing date format-
2) Growth measure
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.
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?
Hi. I have updated the code to eliminate M1. It was a test measure created using.
M1 = Sum('Table'[#Engaged])
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |