cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## MoM change giving wrong values

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]

2 ACCEPTED SOLUTIONS
Frequent Visitor
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 )
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

BR

Frequent Visitor
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 )