Forum Discussion
How not to exclude data comparing two sets of data with dates
I have two tables: one reflecting budget values, the other reflecting actual values, each with daily values. For ease, let's say each table only has "date" and "unit sales" columns. In my PBI matrix, I have sums of each by month. The matrix appears to be driven by the actual dataset (as "key"). Using a representative week (say, Jan 1-Jan 7), let's assume I have a budget of 2 units per day. However, when matched to actuals, it excludes budget numbers from days where there were no actuals. Please see table below:
Each data table is mapped to a standard date dimension table (one-directional, many-to-one). Date field is "Date" from dimension table; actual and budget values are "unit sales" from respective data tables.
| Date | Actual | Budget |
| 1/1 | 3 | 2 |
| 1/2 | 2 | |
| 1/3 | 1 | 2 |
| 1/4 | 2 | 2 |
| 1/5 | 1 | 2 |
| 1/6 | 2 | |
| 1/7 | 4 | 2 |
| REAL TOTAL | 11 | 14 |
| PBI MATRIX SHOWS | 11 | 10 |
How can I remedy this so that counts (sums, whatever function) are driven by date, independent of whether or not each data table has data/values on a given date? (If I used "budget" values as my key, I could similarly omit data if there's actual data and no budget value on a select date).
Thank you, Kevin
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.
6 Replies
- KNP
Super User
It 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.
- Kedar_Pande
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.
v-menakakotaCommunity Support
Hi kgsoto ,
Thanks for reaching out to the Microsoft fabric community forum.I would also take a moment to thank Kedar_Pande , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .
Best Regards,
Community Support Team- v-menakakota
Hi kgsoto ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .
Best Regards,
Community Support Team
