Forum Discussion
Table visual with data from Data tab + measures
Hi,
PBI file attached.
Hope this helps.
I think this would be more obtainable using the Matrix visual rather than a Table visual, so you can group the months together better.
A possible solution could be to create a calculated column, and have it structured something like this:
Period =
IF(
'YourDateTable'[YourDateColumn] <= TODAY(),
FORMAT('YourDateTable'[YourYearColumn],"yyyy"),
"Future Period"
)And put this column above your month column in the "Columns" section of your matrix. Then, write a measure that uses your YTD measure and your Estimated measure together with a similiar logic:
YourNewMeasure =
IF(
'YourDateTable'[YourDateColumn] <= TODAY(),
YourYTDMeasure,
Estimated
)And put this in the "Values" section. For the "Rows" section, you can use the "Project Name" column. This should give you the expected results, however the "YTD" column would actually be the "Total" column for that subgroup but would accomplish the same goal.
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.
