Variable columns

[Anonymous]

Good morning

I have the following problem that I do not know if it can be solved:

Monthly they provide an Excel file (exported from an application) with month-to-month expenses and forecasts. That is, for each row there are 12 columns with the allocated expense of each month and 12 columns with the expense forecast for each month.

If for example we take the file from August, it will have details of the expenses from January to August and the updated forecasts from January to December.

But the report to be generated, the columns to be displayed vary with the month to be reported. For example, August would show:

The expense incurred in the month, the expected expenditure of the month, followed by the one incurred from January to August, and followed by the expected expenditure from September to December.

[amitchandak]

@Anonymous , not very clear.

But you need to unpivot the data.

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

Thanks, but that's not the problem. The data at source is correct:

File consists of 25 columns: 1 column with the description of the Service, 12 columns with the expense month by month (January, February, ..., December) and 12 columns with forecast month to month (January, February, ..., December).

The problem is when generating the report that of the 25 columns I have to present only 15:

1. Service description
2. Expenditure of the current month (depending on the month to be reported will be Actual January or Actual February or .... Current December).
3. Forecast of the current month (depending on the month to be reported will be Real Forecast January or Real Forecast February or .... Real Forecast December)
4. Expenses up to the current month (If we are in August: Actual January, Current February, ..., until Actual July) --> In August 9 columns of current
5. Forecasts for the months after the current month (If we are in August: Real Forecast Octover, Real Forecast November and Real Forecast December) --> In August 3 columns of forecast

[Anonymous]

Hi @Anonymous ,

Could you please provide some sample data and your expected result with examples? And if it involves any calculation, please provide the backend logic. Thank you.

Best Regards

Rena

[Anonymous]

Hola Rena,

I sent you a few data, example of the August report and the calculations/logic

Calculations/Logic:

Column	Formula	Example August	Example April
EAC	YTD + ETC	YTD + ETC	YTD + ETC
YTD	Current summation from January, to the month to be reported	Actual January + Actual February + Actual March + Actual April + Actual May + Actual June + Actual July + Actual August	Actual January + Actual February + Actual March + Actual April
ETC	Forecast summarium from the month to be reported + 1, through December	Forecast September + Forecast October + Forecast November + Forecast December	Forecast May + Forecast June + Forecast July + Forecast August + Forecast September + Forecast October + Forecast November + Forecast December
Current Month	Current month to report	Actual August	Actual April
F Current Month	Forecast of the month to be reported	Forecast August	Forecast April
G		Actual January	Actual January
H		Actual February	Actual February
I		ActualMarch	ActualMarch
J		Actual April	Actual April
K		Actual May	Forecast May
L		ActualJune	Forecast June
M		ActualJuly	Forecast July
N		Actual August	Forecast August
O		Forecast September	Forecast September
P		Forecast October	Forecast October
Q		Forecast November	Forecast November
R		Forecast December	Forecast December

Report:

Service	EAC	YTD	ETC	Current Month	F Current Month	A January	A February	A March	A April	A May	A June	A July	A August	F September	F October	F November	F December
Service 1	646.773	504.397	142.376	4.828	45.064	2.163	6.922	105.440	172.578	92.171	85.707	34.587	4.828	76.804	40.607	22.390	2.575
Service 2	1.221.357	976.781	244.576	55.337	20.430	111.174	131.236	167.130	147.266	143.121	114.978	106.538	55.337	79.041	83.586	64.302	17.647
Service 3	1.195.295	998.955	196.340	23.447	61.000	32.135	27.921	444.460	51.347	112.094	256.493	51.056	23.447	62.092	49.291	48.291	36.667
Service 5	44.289	42.106	2.184	1.174	1.256	6.188	7.942	8.569	878	7.666	2.227	7.461	1.174	1.073	910	200	-
Service 4	81.563	52.045	29.519	-	-	30.034	18.074	61.282	5.764	- 64.044	935	-	-	-	-	-	29.519
Total	3.189.277	2.574.283	614.994	84.787	127.750	181.694	192.096	786.880	377.835	291.008	460.340	199.643	84.787	219.010	174.394	135.184	86.406

Data:

Service

Subservice

Forecast January

Forecast February

Forecast March

Forecast April

Forecast May

Forecast June

Forecast July

Forecast August

Forecast September

Forecast October

Forecast November

Forecast December

Actual January

Actual February

Actual March

Actual April

Actual May

Actual June

Actual July

Actual August

Actual September

Actual October

Actual November

Actual December

Service 5	SubService 1	0,00	0,00	0,00	0,00	4361,59	396,53	396,53	396,53	396,53	0,00	0,00	0,00	0,00	0,00	2038,56	3308,99	3965,06	0,00	0,00	0,00	0,00	0,00	0,00	0,00
Service 5	SubService 1	0,00	0,00	0,00	7665,76	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00
Service 5	SubService 2	0,00	0,00	0,00	0,00	306,58	306,58	306,58	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	306,58	0,00	0,00	0,00	0,00	0,00
Service 5	SubService 3	0,00	6482,02	5734,09	3624,94	299,64	5105,86	3416,03	599,14	176,88	422,28	0,00	0,00	0,00	3869,27	3988,93	-3361,15	2817,18	1689,83	6174,50	1173,70	0,00	0,00	0,00	0,00
Service 5	SubService 12	0,00	2730,00	2730,00	2730,00	2730,00	797,50	557,93	260,38	500,00	488,00	200,00	0,00	6188,07	4073,16	2541,06	930,64	884,01	537,12	980,06	0,00	0,00	0,00	0,00	0,00

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) base on your sample data, please check whether that is what you want.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Thanks a lot!! Solved!!