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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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

15 REPLIES 15
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/

Hello Ashish

 

I also have same requirement as PKALISZ.

I need to calculate MOM only for the last month as shown below

 

data.png

Thanks

Chetan

Hi,

Share data in a format that can be pasted in an MS Excel file.


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

Hi ashish

 

here is the data

 

order dateorder qtyreturn qty
01-01-202462
02-01-202472
01-02-202481
02-02-202463
01-03-202473
02-03-202482

 

Here is the report i need

 

report.png

Thank you much

Chetan

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713313626966.png

 


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

Thank you very much...it works

I forgot to mention that I also needed YoY next to MoM

 

order dateorder qtyreturn qty
01-03-202362
02-03-202373
01-01-202462
02-01-202472
01-02-202481
02-02-202463
01-03-202473
02-03-202482

 

here is the report

report.png

 

Thanks

Chetan

You are welcome.  I do not know how to do that.


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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.