Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Bullet_Train95
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)

 

Bullet_Train95_1-1663208785804.png

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

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 )

View solution in original post

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

BR

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 )

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors