Forum Discussion
Table visual with data from Data tab + measures
Hi,
I have below data.
| Project name | 1-May-25 | 1-Jun-25 | 1-Jul-25 | 1-Aug-25 | 1-Sep-25 | 1-Oct-25 | 1-Nov-25 | 1-Dec-25 | 1-Jan-26 |
| A | 23 | 39 | 14 | 20 | 14 | 17 | 33 | 35 | 36 |
| B | 14 | 37 | 28 | 35 | 14 | 35 | 17 | 12 | 26 |
| C | 37 | 31 | 40 | 29 | 14 | 12 | 30 | 24 | 25 |
| D | 17 | 24 | 37 | 13 | 29 | 27 | 31 | 33 | 15 |
| E | 32 | 18 | 40 | 16 | 24 | 15 | 15 | 36 | 33 |
I want to create a Table visual as below. he YTD and Estimated will be calculated values through measure. I am unable to understand how to add the calculated measures to the table visual. Can u pls help as I am new to this type of use case-
| YTD | Estimated | ||||||||||||
| Project name | 1-May-25 | 1-Jun-25 | 1-Jul-25 | 1-Aug-25 | 1-Sep-25 | 1-Oct-25 | 1-Nov-25 | 1-Dec-25 | 1-Jan-26 | 1-Mar-26 | 3-Apr-26 | 4-May-26 | |
| A | 23 | 39 | 14 | 20 | 14 | 17 | 33 | 35 | 36 | 36 | 35 | 40 | 22 |
| B | 14 | 37 | 28 | 35 | 14 | 35 | 17 | 12 | 26 | 26 | 14 | 39 | 25 |
| C | 37 | 31 | 40 | 29 | 14 | 12 | 30 | 24 | 25 | 25 | 27 | 21 | 19 |
| D | 17 | 24 | 37 | 13 | 29 | 27 | 31 | 33 | 15 | 15 | 27 | 29 | 12 |
| E | 32 | 18 | 40 | 16 | 24 | 15 | 15 | 36 | 33 | 33 | 26 | 23 | 26 |
Hi,
PBI file attached.
Hope this helps.
12 Replies
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.
Hi,
If Table 1 shows the shape of the input data table, then you will have to first "Unpivot Other Columns" in Power Query and then build a matrix visual. But in the matrix visual, if you want to add 4 additional columns (YTD and the last 3 column of your Table 2), then you may have to use calculation groups.
Can u pls help me to understand how the calculation groups can help me to achieve this?
Hi,
Could you share sample data and the logic of your forecast calculations.
Hi harshadrokade ,
If possible, could you please provide more details about your data?
How to provide sample data in the Power BI Forum
You can refer the following link to upload the file to the community.
How to upload PBI in CommunityThank you.
The data shared in oroginal post can be considered as the data
