Need to get the running total and present it for each month per project.

[AstaBr]

I need to recreate a table below from an excel sheet. The highlighed column is the October month running total which comes from the pivot table (snip 2):

I am using 'Dashboard MM Active Financials' table. The column 'Dashboard_Finance Input Type' has rows of 'Planned' and 'Actual/Forecast' which corresponds to the values in column 'Dashboard_Monthly value'.
The months M1, M2... stands for fiscal months (April till previous month full Actual value). The Total Value below is the sum of all the financial year 'Actual/Forecast' values. Every month figures are updated which will go in Actual row but Forecast figure will still be counting for the future months.

I created date column in the data sheet called 'fiscalYearMonth'. 
'Dashboard_Project' column has all project names. 

'Dashboard_Monthly value' has unpivoted individual months values in 1 column.

[lbendlin]

I need to recreate a table below from an excel sheet.

First rule of Power BI:  Don't try to make it behave like Excel. You will fail.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[AstaBr]

Hi, let me try to explain it all again what needs to happen:

From the 'Dashboard MM Active Financials' table I need to have Matrix table to show every month's Project Total value (as per the snip) which needs to be lock in for each Total value. We have column for 'fiscalYearMonth' that has date in it (e.g. 01/04/2024); we have column for 'Dashboard_Month' which is sorted by Fiscal Month Order column. We have column 'Dashboard_Monthly value' that has values for each Project and is Unpivoted values (in the data set you will see Each month Name in column having the values in the rows; we have column 'Dashboard_Finance Input Type' that has rows for Planned and Actual/Forecast, out of which we only need to get values for Actual/Forecast. We need to have calculation to show like the table attached. e.g. April values for all Projects in column which we get from:
April Actual figure + the rest months (May till March) Forecasted values. Then April + May Actual values + the rest of the month’s Forecast values and so on.

e.g. the Total value for April is 142,475 we got this by adding the actual figure for April (32475) + 100000 (10 000 x remaining 11 months Forecast) for the rest of the months (this amount was changed later on to 15000 per month instead of forecasted 10000).
May Total value was 161887 which we got adding the actual figures for April + May + the rest of the months (10 months x 10000).
The Total value for Actual months needs to be presented individually every month to see the progress and should not get changed (needs to be locked in). The previous values should be taken for calculating future months figures but months that were locked in can't be affected by the future changes in the Forecast values.
Finally, the Planned Total value has to be added to this visual as well which will never change.

I have 2 tables in PowerBi, the original 'MM Active Financials' and its duplicated table renaimed to 'Dashboard MM Active Financials' which has Unpivoted Months £ values in 1 column named 'Dashboard_Monthly value' and all months unpivoted to column 'Dashboard_Month'. I only included small number of data due to space.

Project	Financial Year	Finance Input Type	Total Value	Apr £	May £	Jun £	Jul £	Aug £	Sept £	Oct £	Nov £	Dec £	Jan £	Feb £	Mar £
{pending archive} 24/25 - Takeda Vedolizumab Savings Gain Share #	2024/2025	Planned	126000	0	0	0	14000	14000	14000	14000	14000	14000	14000	14000	14000
{pending archive} 24/25 - Takeda Vedolizumab Savings Gain Share #	2024/2025	Actual/Forecast	0	0	0	0	0	0	0	0	0	0	0	0	0
24/25 - Apixaban Generic	2024/2025	Planned	110000	0	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000
24/25 - Apixaban Generic	2024/2025	Actual/Forecast	290188	32475	29412	27562	33393	31440	29009	31897	15000	15000	15000	15000	15000
24/25 - BREDI (NR)	2024/2025	Planned	54999	4583	4583	4583	4583	4583	4583	4583	4583	4584	4584	4584	4583
24/25 - BREDI (NR)	2024/2025	Actual/Forecast	69953	4583	4583	4583	4583	19537	4583	4583	4583	4584	4584	4584	4583
24/25 - Contract Variance savings	2024/2025	Actual/Forecast	112677	10333	10340	13641	34445	-11634	3735	1817	10000	10000	10000	10000	10000
24/25 - Contract Variance savings	2024/2025	Planned	-75996	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333
24/25 - Contract Variance savings	2024/2025	Planned	200000	16666	16666	16666	16666	16667	16667	16667	16667	16667	16667	16667	16667
24/25 - Contract Variance savings	2024/2025	Actual/Forecast	0	0	0	0	0	0	0	0	0	0	0	0	0
24/25 - Dexamethasone switch from soluble to standard tablets	2024/2025	Planned	15000	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250

[lbendlin]

What is the starting month for your fiscal year?

Your first step is to unpivot the data, like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZTLTsMwEEV/ZVQ2IFHhZxwvC4WKDUKAukFdTBNDIlKnSlxAIP4du6EU8WhLH5GsO7Y8mpO5tm9vW69jY9Pc3gNWSZY/mjdg4ohJaMMNPpgUoW/SsshfJiMcwjU++q019DC3cJ1hZWCvddhiJOQQn+UnlwVaa1IfURYRQnzwdVDRrG2mg8MdkHcSN8Hi6KysTIK1+wa+2ghgM4zOOH/GIVroGWuqPFnQKUrmnZrF29X/gP1sBNOExrEPOBNKThcEZUGVjIJyzjUPSoUgTQLR03msVYCQHzAL9Svk8dVp9xz2L64O/u6bFFqHKkLGfE0RvwlfkeRnoyKt5bKq1G9RmyGdlNZVmDjoY5WjTQzUzQFfTkj9tVRTS4g3rdGpZZRHggbLhBDB4jalEQ/VueJhTmOq1jpuK8POjW0rqXUUgqiB3I1uSMnI7J8j/y1RtbZuyfjNHrOuecaRcRnWpfVFn3KXZHBXlSOoy2IyLAy4EmqHNsUqBYd+xS1o3ee9Z3LLMhi8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Financial Year" = _t, #"Finance Input Type" = _t, #"Total Value" = _t, #"Apr £" = _t, #"May £" = _t, #"Jun £" = _t, #"Jul £" = _t, #"Aug £" = _t, #"Sep £" = _t, #"Oct £" = _t, #"Nov £" = _t, #"Dec £" = _t, #"Jan £" = _t, #"Feb £" = _t, #"Mar £" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Project", "Financial Year", "Finance Input Type", "Total Value"}, "Month", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns"," £"," 1 2024",Replacer.ReplaceText,{"Month"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Value", Currency.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[AstaBr]

I did mentioned before that i have 2 data tables (2nd 1 is a duplicate to unpivot the April £ 2024 - March £ 2025 values.

The starting Month of Fiscal Year is April.