month to month % difference

[Fidzi8]

Hi,
I need help.
How I calculate new measure for month to month % difference?

Thank you
Ondřej

[amitchandak]

@Fidzi8 , we can use time intelligence for month on month

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

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

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

[Fidzi8]

Thank you @amitchandak 

I create your measures and the sub-part works but the overall result is bad.

How can I adjust the measures to work.

Thank you
Ondřej

[v-chenwuz-msft · Accepted solution]

Hi @Fidzi8 ,

Maybe you can use HASONEVALUE() to control the total row.

Some code like the following:

Diff =
VAR _last =
    CALCULATE(
        SUM( 'Table'[Cost] ),
        DATESMTD( DATEADD( 'Table'[Month], -1, MONTH ) )
    )
VAR _diff =
    DIVIDE ( SUM( 'Table'[Cost] ) - _last , _last)
VAR _maxmonth =
    CALCULATE(
        SUM( 'Table'[Cost] ),
        MONTH( 'Table'[Month] ) = MONTH( MAX( 'Table'[Month] ) )
    )
VAR _maxmonth_1 =
    CALCULATE(
        SUM( 'Table'[Cost] ),
        MONTH( 'Table'[Month] )
            = MONTH( MAX( 'Table'[Month] ) ) - 1
    )
RETURN
    IF(
        HASONEVALUE( 'Table'[Month] ),
        _diff,
        DIVIDE( _maxmonth - _maxmonth_1, _maxmonth_1 )
    )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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