MoM change giving wrong values

[Anonymous]

I'm trying to create a table that gives me the MoM percentage change after each month. I'm close but getting strange results on in my table.

I've created a measure for the current months value, previous month's value, the difference. See results below. (note - these values are already percentages before going into this table)

For example, shouldn't 2022 May be showing -19.21% under DIFF, not -0.33%?

Current MTD Efficiency = CALCULATE( [Waste Diverted Percentage - spoil] , DATESMTD( 'Contractor Monthly Report Data'[Confirmed Date].[Date]))

Last MTD Efficiency = CALCULATE( [Waste Diverted Percentage - spoil] , DATESMTD( DATEADD('Contractor Monthly Report Data'[Confirmed Date].[Date], -1, MONTH)))

diff = [Current MTD Efficiency] - [Last MTD Efficiency]

[Anonymous · Accepted solution]

Waste Diverted Percentage - spoil =

VAR Net_WasteDisposal = [Total_WasteDisposal] - [Managed_Fill_Total]

VAR Waste_Efficiency = DIVIDE( [Total Waste Diverted - Spoil], Net_WasteDisposal + [Total_WasteDiverted]   )

return IF( ISBLANK( Waste_Efficiency) , 0, Waste_Efficiency )

[Ashish_Mathur · Accepted solution]

Hi,

You should create a Calendar Table with calculated column formulas fror Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship from the __- date to the Date column of the Calendar Table.  To your visuals, drag Year and Month name from the Calendar Table.  Write these measures

Last MTD Efficiency = CALCULATE( [Waste Diverted Percentage - spoil],previousmonth(Calendar[Date]))

diff = [Waste diverted percentage - spoil] - [Last MTD Efficiency]

Hope this helps.

[Anonymous]

Hi 
@Anonymous  are you able to share the measure for "Waste Diverted Percentage - Spoil"

BR

[Anonymous · Accepted solution]

Waste Diverted Percentage - spoil =

VAR Net_WasteDisposal = [Total_WasteDisposal] - [Managed_Fill_Total]

VAR Waste_Efficiency = DIVIDE( [Total Waste Diverted - Spoil], Net_WasteDisposal + [Total_WasteDiverted]   )

return IF( ISBLANK( Waste_Efficiency) , 0, Waste_Efficiency )