Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |