Yet another MTD question!

Question

Yet Another MTD Question! (udpated)&nbsp;Dropbox File&nbsp;With the forum's help I was able to solve a couple of related questions, but this one has me stumped.&nbsp; The MTD values (BTZ Feed Ozs MTD) do not equal the cumulative daily values (BTZ Feed Ozs).&nbsp; See sheet below: Grand Total MTD = 3714 &amp; cumulative daily = 3870.&nbsp;BTZ Feed Ozs = Btz Tons * BTZ 30day avg (trailing 30 day avg)&nbsp;Current Result:&nbsp; &nbsp;&nbsp; &nbsp; &nbsp;BTZ Feed Ozs MTD = Btz Tons (MTD) * BTZ 30day avg (daily)&nbsp;Desired Result&nbsp; &nbsp; &nbsp;BTZ Feed Ozs MTD = sum of each day's BTZ Feed Ozs&nbsp;&nbsp;&nbsp;

greg_deckler · Answer

Might be wrong, bu this looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

troyhimes · Answer

Thanks for the links Greg, I wasn't aware of the HASONExxxxx functions.

I may not be seeing it, but I don't think it's the solution to this issue though. The Grand Total value is not used for anything, I'm just displaying it for illustration purposes in this post. My issue is with the MTD calculation.....I don't know if I'm using the correct terminology but how do I keep the row to row context when adding the daily values for the MTD calc?

As expected, the 3/1 daily BTZ Feed Ozs calc uses the row context (3/1 Btz Tons * 3/1 BTZ 30day avg) as does the MTD.

3/1 Daily=312 & MTD=312

On subsequent days (example 3/2), again as expected the 3/2 daily BTZ Feed Ozs calc uses the row context (3/2 Btz Tons * BTZ 30day avg) BUTthe MTD calc uses (Cumulative Feed Ozs * Current Row daily 30day avg ----> 3/1 + 3/2 BTZ Feed Ozs * 3/2 BTZ 30day avg)

3/2 Daily=271 & MTD=591

The daily values return 312+271=583 but my MTD value is returning 591 based on the calc above.

anonymous · Answer

// Calendar must be the Date table in the model
// marked as such.

[BTZ Feed Ozs MTD] =
	sumx(
		DATESMTD( Calendar[Date] ),
		[Btz Tons] * [Btz 30day avg]
	)
&nbsp;
Best
D

troyhimes · Answer

Anonymous&nbsp;, based on your response, my "row context" was probably incorrectly used and I am using the Date Table.&nbsp;The BTZ 30day avg is a measure and here's the formulas...&nbsp;BTZ Feed Ozs:=sum(Conccentrator_Final[Blitz Tons])*[BTZ 30day avg]&nbsp;BTZ Feed Ozs MTD:=CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [BTZ Feed Ozs],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATESMTD( 'Calendar'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Conccentrator_Final[Feed Oz's_2] &gt; 0)

troyhimes · Answer

Anybody have any other suggestions?&nbsp; For more clarification, here's the measures I'm using.&nbsp;BTZ Feed Ozs MTD:=CALCULATE ([BTZ Feed Ozs],DATESMTD( 'Calendar'[Date] ),Conccentrator_Final[Feed Oz's_2] &gt; 0)BTZ Feed Ozs&nbsp;:=SUM ( Conccentrator_Final[Blitz Tons] ) * [BTZ 30day avg]BTZ 30day avg&nbsp;:=CALCULATE (AVERAGE ( 'Data input'[PtPd] ),'Data input'[Region] = "BLITZ",DATESINPERIOD ('Calendar'[Date],LASTDATE ( 'Calendar'[Date] ),- [Days in Avg Calc],DAY))

Forum Discussion

Yet another MTD question!

5 Replies

Related content

Another Power BI licencing question

Bookmark questions

another cross directional relationship question

Architecture question and brainstorming

Another RLS question - default roles