Forum Discussion
How not to exclude data comparing two sets of data with dates
Budget Units =
CALCULATE(
SUM(Budget[Unit Sales]),
REMOVEFILTERS(Actual[Date]), // ignore actuals filter
USERELATIONSHIP(Date[Date], Budget[Date]) // force budget relation
)Actual Units =
CALCULATE(
SUM(Actual[Unit Sales]),
REMOVEFILTERS(Budget[Date]), // ignore budget filter
USERELATIONSHIP(Date[Date], Actual[Date])
))
Matrix: Rows = Date[Month], Values = both measures.
Super UserIt shouldn't be behaving the way you describe.
I've just recreated what I've understood from your example, and it is working as I would expect.
There must be something more complex going on.
KNP, you are correct, there is more. I have my data in a full-year table, where I'm calculating MTD values. In completed months, MTD Count should = Full Month Count (Bud Units below = MTD). But in the instance above, this is not true. Below is an actual screen shot:
And looking into the detail, my original question (hypothesis) is wrong. It's not excluding mismatched Act vs Bud dates, but it is excluding Bud units after a date. Below is screenshot of Act and Bud units by date, for the same criteria as above. Pink is budgeted, no corresponding actual data by date, but included in MTD total; yellow is budgeted, and excluded from MTD Feb sum (ties to 240 in above table).
With all that in mind, my new direction is this: my MTD calculation is flawed in that it excludes "extra" dates - as driven by actuals. In Jan, last actual is 1/30, last budget value is 1/30, so monthly totals tie (and this eliminates my thought that current MTD "day" i.e. 26th - not "date" - was impacting the math, but it's not).
More broadly, I'm using MAXDATE from my actual data to drive MTD calc (actual data is usually a day behind reality - so, at present, my Actual dataset stops at 3/25). MTD has been a thorn in my side and maybe that's where I should have started...
- Ashish_Mathur
Hi,
Share the download link of the PBI file with your visual/measures already there. Show the problem there clearly.
