How to create a summary table for each month running total for fiscal year

[AstaBr]

Hi,

I have a data table called 'MM Active Financials' and manually created 'DateTable' connected with relationship.

The 'Projects' has Planned' and 'Actual/Forecast' values entered monthly, both of them sits under 'Finance Input Type' column . Financial year starts from Aprl - March. I need to recreate a Summary table (snip attached) of Pivot table used before to show all months (From April till previous month as we present findings for completed months only) with the 'Total Value' column from 'Actual/Forecast'. 

We need to create a automated process for current months to appear automatically please.

Not sure if the relationship is correctly linked:

Project Reference (Project) (Project)	Project	Estimate	Months To Lock	Financial Year	Phasing start date	Finance Input Type	Total Value	Apr £	May £	Jun £	Jul £	Aug £	Sept £	Oct £	Nov £	Dec £	Jan £	Feb £	Mar £	YTD
R1K-ISCR-2023-0011	24/25 - Apixaban Generic		4	2024/2025	01/04/2024	Planned	110000	0	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	60000
R1K-ISCR-2023-0011	24/25 - Apixaban Generic	120000	4	2024/2025	01/04/2024	Actual/Forecast	290188	32475	29412	27562	33393	31440	29009	31897	15000	15000	15000	15000	15000	215188
R1K-SURP-2024-0003	24/25 - BREDI (NR)		4	2024/2025	01/04/2024	Planned	54999	4583	4583	4583	4583	4583	4583	4583	4583	4584	4584	4584	4583	32081
R1K-SURP-2024-0003	24/25 - BREDI (NR)	0	4	2024/2025	01/04/2024	Actual/Forecast	69953	4583	4583	4583	4583	19537	4583	4583	4583	4584	4584	4584	4583	47035
R1K-SURP-2024-0001	24/25 - Contract Variance savings	0	4	2024/2025	01/04/2024	Actual/Forecast	112677	10333	10340	13641	34445	-11634	3735	1817	10000	10000	10000	10000	10000	62677
R1K-SURP-2024-0001	24/25 - Contract Variance savings		4	2024/2025	01/04/2024	Planned	-75996	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-6333	-44331
R1K-SURP-2024-0001	24/25 - Contract Variance savings		4	2024/2025	01/04/2024	Planned	200000	16666	16666	16666	16666	16667	16667	16667	16667	16667	16667	16667	16667	116665

[Anonymous]

Hi @AstaBr ,

First we may need to consider doing some transformations to the source table, please refer to the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZTfS8MwEMf/lbInhZXd5S5J8zjnD4Ygo0Nfxh5iLVIYnXRV/PPNZRP2MJ3tZih875prLp/m2y4Wgxzv0+l8kqcKFKUAiIPhQPFI6SRNxm/Vp3/2dXJX1mVTFWEqXCwVIDWgdIgBRxATmZitfF2XLyFChDBkXpJdfG41UZfD7iCodkv8ijMu2ne/Gt2um7Lwm1ZKHWCWhYAUWx1vMCpRq40oETkSRWbYPgAu5pmz0ljvEI6oQi2Nvtnmj/lM2DiwAe2xXeU319Pk4iG/7HQ8mp2TbbHOqKfwIYnoCjLsuPMeR2Gc08e2iaHE9mFgC6QPM+w7a7Ku28YXbfLkm8rXRZls/EdVv276ISEqY6NLIPhoq9FFSIalLzGzrJMiGpI1yJLkmKHt8t3ENifR/d1qqdXOGQnMFurflJnoB+OdHyv+QOJLNWEcUXuKShC8uPwC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Reference (Project) (Project)" = _t, Project = _t, Estimate = _t, #"Months To Lock" = _t, #"Financial Year" = _t, #"Phasing start date" = _t, #"Finance Input Type" = _t, #"Total Value" = _t, #"Apr £" = _t, #"May £" = _t, #"Jun £" = _t, #"Jul £" = _t, #"Aug £" = _t, #"Sept £" = _t, #"Oct £" = _t, #"Nov £" = _t, #"Dec £" = _t, #"Jan £" = _t, #"Feb £" = _t, #"Mar £" = _t, YTD = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Total Value", "YTD"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Project Reference (Project) (Project)", "Project", "Estimate", "Months To Lock", "Financial Year", "Phasing start date", "Finance Input Type"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each let
month = Text.Replace(Text.Split([Attribute]," "){0},"Sept","Sep") ,
year = Text.Split([Financial Year],"/"),
result = if List.Contains({"Jan","Feb","Mar"},month) then year{1} & "-" & month & "-" & "1" else year{0} & "-" & month & "-" & "1"
in
result),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Estimate", Int64.Type}, {"Months To Lock", Int64.Type}, {"Phasing start date", type date}, {"Value", Int64.Type}, {"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Finance Input Type2", each let 
month = Date.Month([Date]),
fiscalmonth = if month < 4 then 9+month else month-3,
result = if [Finance Input Type] <> "Planned" then [Finance Input Type] & " M" & Text.From(fiscalmonth) else [Finance Input Type]
in 
result),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Finance Input Type2 Order", each if [Finance Input Type] <> "Planned" then let month = Date.Month([Date]),fiscalmonth = if month < 4 then 9+month else month-3 in fiscalmonth else 0)
in
    #"Added Custom2"

And then create a new measure:

Measure = 
VAR __date = EOMONTH(TODAY(),-1)
VAR __max_date = MAX('MM Active Financials'[Date])
VAR __cur_type = SELECTEDVALUE('MM Active Financials'[Finance Input Type2])
VAR __result = IF( __cur_type="Planned" || __date>=__max_date, SUM('MM Active Financials'[Value]))
RETURN
__result

Attached is the pbix file for reference, hope this helps.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[AstaBr]

I forgot to say that I had to duplicate the 'MM Active Financials' table due to Unpivoting. I have other created tables that I need individual Months (Apr £, May £, etc) as well as YTD for monthly figures to be presented. By duplicating tables, i am worried that the relationship might not work correctly going forward.

[AstaBr]

Hi and thank you so much for trying to help @Anonymous ;

I managed to recreate your steps on my dashboard however, the totals you are getting is only the monthly values while i need a running total for each month which needs to have the Total Actual values and Forectastes values showing. Please see the snip attached maybe that will explain more:

My recreated measure from you is:

Measure =

VAR __date = EOMONTH(TODAY(), -1)

VAR __max_date = MAX('MM Active Financials (2)'[Date])

VAR __cur_type = SELECTEDVALUE('MM Active Financials (2)'[Finance Input Type2])

VAR __result =

    IF(

        __cur_type = "Planned" || __date >= __max_date,

        SUM('MM Active Financials (2)'[Value])

    )

RETURN

__result

Please could the 'Total' column at the end not be included, not sure how it came as i did try using your steps. Also, If 'Planned' could be at the front:

I am very gratful for your help.