Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Yet Another MTD Question! (udpated) Dropbox File
With the forum's help I was able to solve a couple of related questions, but this one has me stumped. The MTD values (BTZ Feed Ozs MTD) do not equal the cumulative daily values (BTZ Feed Ozs). See sheet below: Grand Total MTD = 3714 & cumulative daily = 3870.
BTZ Feed Ozs = Btz Tons * BTZ 30day avg (trailing 30 day avg)
Current Result:
BTZ Feed Ozs MTD = Btz Tons (MTD) * BTZ 30day avg (daily)
Desired Result
BTZ Feed Ozs MTD = sum of each day's BTZ Feed Ozs
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
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.
// 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]
)
Best
D
@Anonymous , based on your response, my "row context" was probably incorrectly used and I am using the Date Table.
The BTZ 30day avg is a measure and here's the formulas...
BTZ Feed Ozs:=sum(Conccentrator_Final[Blitz Tons])*[BTZ 30day avg]
BTZ Feed Ozs MTD:=CALCULATE (
[BTZ Feed Ozs],
DATESMTD( 'Calendar'[Date] ),
Conccentrator_Final[Feed Oz's_2] > 0
)
Anybody have any other suggestions? For more clarification, here's the measures I'm using.
BTZ Feed Ozs MTD:=CALCULATE (
[BTZ Feed Ozs],
DATESMTD( 'Calendar'[Date] ),
Conccentrator_Final[Feed Oz's_2] > 0
)
BTZ Feed Ozs :=
SUM ( Conccentrator_Final[Blitz Tons] ) * [BTZ 30day avg]
BTZ 30day avg :=
CALCULATE (
AVERAGE ( 'Data input'[PtPd] ),
'Data input'[Region] = "BLITZ",
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
- [Days in Avg Calc],
DAY
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |