Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
Marico
Helper III
Helper III

Displaying values in matrix which are outside of selected slicer range

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.

     

    Marico_2-1737460818533.png

     

     

    Marico_1-1737459070639.png

     

    Any help would be appreciated. Thank you!

     

3 REPLIES 3
Marico
Helper III
Helper III

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

Sahir_Maharaj
Super User
Super User

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

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.