Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
This is in continuation to the following post:
https://community.fabric.microsoft.com/t5/Desktop/Combining-matrix/m-p/4347797#M1359289
I have created the below measure to cover the following scenarios:
More Than Two Months Selected: Uses the latest and second-latest months to calculate the percentage change.
Measure:
volume MoM% =
VAR _latestMonth = MAX(smart_meter_volume_hive[snapshot_date])
VAR _previousMonth = CALCULATE(
MAX(smart_meter_volume_hive[snapshot_date]),
smart_meter_volume_hive[snapshot_date] < _latestMonth
)
VAR _latestMonthVolume = CALCULATE(
SUM(smart_meter_volume_hive[volume]),
smart_meter_volume_hive[snapshot_date] = _latestMonth
)
VAR _previousMonthVolume = CALCULATE(
SUM(smart_meter_volume_hive[volume]),
REMOVEFILTERS(Date_Table[Current Month-Year]),
smart_meter_volume_hive[snapshot_date] = _previousMonth
)
/*VAR __PREV_MONTH = CALCULATE(
SUM('smart_meter_volume_hive'[volume]),
PREVIOUSMONTH(smart_meter_volume_hive[snapshot_date]),ALL(Date_Table[Current Month-Year])
)*/
RETURN
IF(
_previousMonthVolume <> BLANK(), DIVIDE(_latestMonthVolume - _previousMonthVolume, _previousMonthVolume, 0), BLANK()
)
/* DIVIDE(
SUM('smart_meter_volume_hive'[volume]) - __PREV_MONTH,
__PREV_MONTH
)*/
Although this measure is displaying the correct value for MoM% change, we need to update the measure to calculate the percentage change even if the previous month exists in the dataset but is not selected in the slicer. The key point here is to ensure that even when only one month is selected or the slicer does not contain the previous month, the calculation will still consider the previous month's data and show the correct percentage change.
Any help would be appreciated. Thank you!
@amitchandak @Greg_Deckler @danextian @Ritaf1983 @lbendlin @rajendraongole1 @DataNinja777 Do you have idea on how to solve this problem?
Hello @Marico,
Can you please try this approach:
volume MoM% =
VAR _latestMonth = MAX(smart_meter_volume_hive[snapshot_date])
VAR _previousMonth =
CALCULATE(
MAX(smart_meter_volume_hive[snapshot_date]),
REMOVEFILTERS(Date_Table[Current Month-Year]),
smart_meter_volume_hive[snapshot_date] < _latestMonth
)
VAR _latestMonthVolume =
CALCULATE(
SUM(smart_meter_volume_hive[volume]),
smart_meter_volume_hive[snapshot_date] = _latestMonth
)
VAR _previousMonthVolume =
CALCULATE(
SUM(smart_meter_volume_hive[volume]),
REMOVEFILTERS(Date_Table[Current Month-Year]),
smart_meter_volume_hive[snapshot_date] = _previousMonth
)
RETURN
IF(
NOT ISBLANK(_previousMonthVolume),
DIVIDE(_latestMonthVolume - _previousMonthVolume, _previousMonthVolume, 0),
BLANK()
)
@Sahir_Maharaj @Thanks for sharing this. However, adding remove filters condition didn't give the correct calculation result
I tried to print _previousmonth and it gave incorrect dates. For e.g. if remove filters is applied and in slicer, aug-2024 and oct -2024 are selected (non consecutive months) then for aug-2024, it shows july-2024 but oct-2024 shows previous month as sept-2024, although basis the slicer selection, oct-2024 should show aug-2024 as previous month.
Any thoughts on this?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |