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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pkalisz
Frequent Visitor

Table visual with MoM measure only for last month

I'm trying to create a table/matrix visual where given data, I add a column that is MoM comparison, but only for the last month.

Given a Dummy table, I'd like my visual to present like that in April:

pkalisz_1-1679765511279.png

 

Dummy table in April:

pkalisz_0-1679765415257.png


The month before, upon no data of April, the visual would look like below:

pkalisz_2-1679765601050.png

 

 

I am able to create MoM measure for all months, and even only for the latest month, using methods similar to presented here.

The problem is that when I use that as a measure, in the best case, I can only get an empty MoM column for previous months. The goal is to have MoM column only at the end, and only for the most recent period.

pkalisz_3-1679765908813.png

 

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@pkalisz The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello Ashish - can you please show the measure or how you did this? Your PBI file is no longer available to access and this would be very helpful for me. Thank you!

Hi,

I do not have the file.  Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The data I would share would be very similar to the data originally posted by pkalisz

I'm trying to create a table/matrix visual where given data, I add a column that is MoM comparison, but only for the last month.

Given a Dummy table, I'd like my visual to present like that in April:

gmetzger_0-1702044859700.png

 

 

Dummy table in April:

gmetzger_1-1702044859700.png

 

The problem is that when I use that as a measure, in the best case, I can only get an empty MoM column for previous months. The goal is to have MoM column only at the end, and only for the most recent period.

I'm also using a slicer to display date columns from a calendar table joined to my data. With this DAX - I am able to show MoM for last (2) months if ONLY those two months are filtered for matrix. BUT if I expand the slicer to include a 3rd month, the calculation is off: 

CalcMoM =
IF (
    HASONEVALUE('Volume_Schedule_DataSumm'[Volume_Type]) && HASONEVALUE('Calendar'[Year Month]),
    SUM('Volume_Schedule_DataSumm'[Volume]),
    VAR CurrentMonth = MAX('Calendar'[Year Month])
    VAR EarlierMonth = CALCULATE(MAX('Calendar'[Year Month]), ALLSELECTED('Calendar'), 'Calendar'[Year Month] < CurrentMonth)
    VAR CurrentMonthVolume = CALCULATE(SUM('Volume_Schedule_DataSumm'[Volume]), 'Calendar'[Year Month] = CurrentMonth)
    VAR EarlierMonthVolume = CALCULATE(SUM('Volume_Schedule_DataSumm'[Volume]), 'Calendar'[Year Month] = EarlierMonth)
    RETURN
        IF(
            ISINSCOPE('Calendar'[Year Month]),
            IF(
                HASONEVALUE('Calendar'[Year Month]),
                SUM('Volume_Schedule_DataSumm'[Volume]),
                CurrentMonthVolume - EarlierMonthVolume
            ),
            EarlierMonthVolume - CurrentMonthVolume
        )
)

This one is super easy and intuitive. Thank you so much.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@pkalisz The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Wow, that's a great and fast response, thank you!

Can I do custom hierarchy dynamically, so it doesn't need a manual update?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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