Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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]

 

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago
    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's avatar
    Ashish_Mathur
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    BR

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
      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's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.