Displaying values in matrix which are outside of selected slicer range

[Marico]

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:

• Single Month Selected: Calculates the percentage change between the selected month's volume and the previous month's volume from the dataset, even if the previous month is not part of the slicer selection.
• Two Months Selected: Calculates the percentage change between the latest and second-latest selected months and displays the Month-over-Month percentage change.

• 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!

[Marico]

@amitchandak @Greg_Deckler @danextian @Ritaf1983 @lbendlin @rajendraongole1 @DataNinja777 Do you have idea on how to solve this problem?

[Sahir_Maharaj]

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()
    )

[Marico]

 @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?