Forum Discussion
Table visual with data from Data tab + measures
Hi,
PBI file attached.
Hope this helps.
Thanks a lot. Can u pls explan this with an example?
Lets say I have this subset of data (similiar to what you have, treated as estimation data) in conjuction with some real sales data from months that have already passed
| CustomerID | 1/1/2025 | 2/1/2025 | 3/1/2025 | 4/1/2025 | 5/1/2025 | 6/1/2025 | 7/1/2025 | 8/1/2025 | 9/1/2025 | 10/1/2025 | 11/1/2025 | 12/1/2025 | 1/1/2026 | 2/1/2026 | 3/1/2026 | 4/1/2026 | 5/1/2026 | 6/1/2026 | 7/1/2026 |
| C001 | 7 | 4 | 8 | 4 | 9 | 4 | 8 | 6 | 4 | 3 | 7 | 6 | 12 | 3 | 10 | 14 | 4 | 2 | 1 |
| C002 | 7 | 3 | 3 | 4 | 10 | 1 | 3 | 3 | 13 | 3 | 6 | 1 | 3 | 5 | 11 | 11 | 1 | 0 | 10 |
| C003 | 2 | 4 | 5 | 2 | 1 | 7 | 6 | 4 | 11 | 4 | 5 | 6 | 4 | 11 | 11 | 15 | 13 | 5 | 5 |
| C004 | 7 | 5 | 7 | 7 | 3 | 4 | 0 | 2 | 6 | 5 | 3 | 2 | 4 | 2 | 8 | 3 | 3 | 8 | 9 |
| C005 | 1 | 7 | 0 | 3 | 13 | 5 | 7 | 1 | 11 | 3 | 6 | 2 | 0 | 2 | 7 | 9 | 11 | 4 | 7 |
| C006 | 3 | 2 | 0 | 2 | 8 | 3 | 10 | 3 | 5 | 3 | 3 | 1 | 13 | 2 | 8 | 3 | 8 | 7 | 14 |
| C007 | 1 | 6 | 9 | 7 | 2 | 4 | 15 | 7 | 11 | 3 | 1 | 3 | 6 | 13 | 0 | 0 | 6 | 1 | 0 |
| C008 | 1 | 7 | 2 | 7 | 12 | 4 | 6 | 3 | 4 | 2 | 3 | 7 | 15 | 1 | 6 | 15 | 3 | 3 | 3 |
| C009 | 6 | 4 | 9 | 7 | 1 | 4 | 5 | 7 | 10 | 2 | 4 | 2 | 10 | 11 | 13 | 3 | 6 | 13 | 2 |
| C010 | 1 | 2 | 10 | 1 | 11 | 6 | 12 | 2 | 9 | 2 | 3 | 6 | 15 | 14 | 3 | 3 | 12 | 14 | 8 |
Within Power Pivot (the "transform" section) I'll unpivot this data to be like this:
| CustomerID | Date | Estimated Sales |
| C001 | 1/1/25 | 7 |
| C001 | 2/1/25 | 4 |
And here's my data model, after transformation and joins (Ensure the "Date" column in "Estimation Data" is a date and not text):
Then write a measure calculating the total amount of estimated and actual sales and combine them into another measure (using my logic from the previous post):
Sales + Estimations =
IF(
SELECTEDVALUE(
'Date'[Period]
) = "Future Period",
[Estimated Sales],
[Total Sales]
)Then put it all into a matrix:
For the desired output (with a few formatting changes):
The real sales data will be used for anything that has already occured, and the estimates will be used for future dates.