Table visual with MoM measure only for last month

[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:

Dummy table in April:

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

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.

[Greg_Deckler · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[gmetzger]

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!

[Ashish_Mathur]

Hi,

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

[chetanbi]

Hello Ashish

I also have same requirement as PKALISZ.

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

Thanks

Chetan

[Ashish_Mathur]

Hi,

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

[chetanbi]

Hi ashish

here is the data

order date	order qty	return qty
01-01-2024	6	2
02-01-2024	7	2
01-02-2024	8	1
02-02-2024	6	3
01-03-2024	7	3
02-03-2024	8	2

Here is the report i need

Thank you much

Chetan

[Ashish_Mathur]

PBI file attached.

Hope this helps.

[chetanbi]

Thank you very much...it works

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

order date	order qty	return qty
01-03-2023	6	2
02-03-2023	7	3
01-01-2024	6	2
02-01-2024	7	2
01-02-2024	8	1
02-02-2024	6	3
01-03-2024	7	3
02-03-2024	8	2

here is the report

Thanks

Chetan

[Ashish_Mathur]

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

[gmetzger]

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:

Dummy table in April:

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.

[gmetzger]

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

        )

)

[pkalisz]

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

[Ashish_Mathur]

You are welcome.

[Greg_Deckler · Accepted solution]

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

[pkalisz]

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

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