Table visual with data from Data tab + measures

[harshadrokade]

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

[v-echaithra]

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 Community

Thank you.

[harshadrokade]

The data shared in oroginal post can be considered as the data

[Ashish_Mathur]

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.

[harshadrokade]

Can u pls help me to understand how the calculation groups can help me to achieve this?

[Ashish_Mathur]

Hi,

Could you share sample data and the logic of your forecast calculations. 

[harshadrokade]

Data is same as I shared in first port.

Logic for Forecasted value-
Assuming I have a column with the actual values for each month:

Month Value

Jan	100
Feb	120
Mar	140
Apr	160
May	180

I will calculate the average monthly growth rate as follows:

1. Calculate the growth rate for each month:

   • Feb: (120 - 100) / 100 = 20%
   • Mar: (140 - 120) / 120 = 16.67%
   • Apr: (160 - 140) / 140 = 14.29%
   • May: (180 - 160) / 160 = 12.5%

2. Calculate the average monthly growth rate:

   • (20 + 16.67 + 14.29 + 12.5) / 4 = 15.89%

3. Calculate the forecast for the 6th month:

   • Forecast = 180 * (1 + 15.89/100) = 208.60

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[harshadrokade]

I wanted to add YTD also After Jan26. 
Also Is there a way to highlight Feb and March as Foorecastred months in header?

[Ashish_Mathur]

Do not know how to add the YTD column.  Not sure of how to colour te header but you can colout the value

[Alex_Sawdo]

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.

[harshadrokade]

Thanks a lot. Can u pls explan this with an example?

[Alex_Sawdo]

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.