Month over Month Change of a Calculated Measure Gives Wrong Result

[Anonymous]

Hello,

I have measure which is a custom running total of annualized sales (sales are related contracts). By saying custom running total, I mean that I`m excluding any sales amount from running total if a contract is expired for a spesific point of time. This measure is working well without an issue, but now I need calculate month over month % changes of this custom annualized sales running total.

I created a MoM% quick measure by using measure I mentioned above, however as you can see in the screenshot below, the MoM% is wrong, and I`m not sure why it`s happening.

In case the screenshot is to small to read, please my measure below:

Annualized Sales Cumulative (Excluding Expired Contracts) =

VAR mindate = MIN ( 'Journal Entries'[Accounting Date].[Date] )

VAR maxdate = MAX ( 'Journal Entries'[Accounting Date].[Date] )

RETURN

CALCULATE (

[Annualized Sales Amount],

ALLSELECTED ( 'Journal Entries' ),

'Contract Details'[Contract Effective Date] <= maxdate,

'Contract Details'[Contract Expiration Date] >= mindate

)

Annualized Sales Cumulative (Excluding Expired Contracts) MoM% =

IF(

    ISFILTERED('Journal Entries'[Accounting Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH =

        CALCULATE(

            [Annualized Sales Cumulative (Excluding Expired Contracts)],

            DATEADD('Journal Entries'[Accounting Date].[Date], -1, MONTH)

        )

    RETURN

        DIVIDE(

            [Annualized Sales Cumulative (Excluding Expired Contracts)]

                - __PREV_MONTH,

            __PREV_MONTH

        )

)

I`d glad if someone can help me to resolve this.

Thank you,

[v-yanjiang-msft]

Hi @Anonymous ,

You can try to write Annualized Sales Cumulative (Excluding Expired Contracts) in a calculated column, then the Annualized Sales Cumulative (Excluding Expired Contracts) MoM% can be this:

Annualized Sales Cumulative  MoM% =
VAR _DIFF =
    'Journal Entries'[Annualized Sales Cumulative (Excluding Expired Contracts)]
        - MAXX (
            FILTER (
                ALL ( 'Journal Entries' ),
                'Journal Entries'[Month]
                    = MAX ( 'Journal Entries'[Month] ) - 1
            ),
            'Journal Entries'[Column]
        )
RETURN
    DIVIDE ( _DIFF, MAX ( 'Journal Entries'[Column] ) )

You can modify the formula appropriately according to your model, it is easier to calculate the decrement by column.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank your response. I tried but the "Annualized Sales Cumulative (Excluding Expired Contracts)" column solution is not working due to the fact that the result of measure changes depending on time perspective (which point of time based on Accounting Date). So my understanding is this measure can't be static in a column. 

Please see a demonstration of the fact table below. If I look this measure:

as of 3/1/2021 Annualized Sales Cumulative (Excluding Expired Contracts) should be calculated as $100,

as of 9/1/2021 Annualized Sales Cumulative (Excluding Expired Contracts) should be calculated as $250,

as of 3/1/2022 Annualized Sales Cumulative (Excluding Expired Contracts) should be calculated as $150

	Contract Effective Date	Contract Expiration Date	Annualized Sales Amount
1-15-2021	1-1-2021	1-1-2022	100
8-31-2021	6-1-2021	6-1-2022	150

Hope it make sense. It`d be great to hear if there is another way to calculate "Annualized Sales Cumulative (Excluding Expired Contracts)" measure to be able to calculate MoM% properly. As I mentioned, I tried to create column but it gave significantly off results.

Than you,

[amitchandak]

@Anonymous , You should use date table and time intelligence

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Learn Power BI Advance Part 3- PowerBI Abstract Thesis: Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI : https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Hi, thank you for your response!

I tried Date table but i received the same results. Also, my original data model is pretty big with multiple dates in my fact table as well in dimension tables. So everytime i try dates table approach, it creates a huge headaches. So it`s not something I`d prefer but even thought I tried it didn't work.