cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate cumulative forecast based on cumulative actuals sum value shown below

Hello All,

I have Actuals and Forecast in two tables. I have appended month wise values as shown in below screen shot.

Appended table has 4 rows Actuals,Forecast, cumulative actuals and cumulative forecast with the months from March 2024-Feb 2025 and go on.

I would like to calculate cumulative forecast taking first value of last existing cumulative actual value(as a first value) whichever it is dynamically. For example, if we are in June month, the total cumulative actual value is 70(should bring cumulative actual value down). The cumulative Forecast need to pick this June Total value and continue Roliing up totals of monthly Forecast values as shown in below screen shot.

Expected output should be in Power query.

The final graph, should be inclined graph cumulative Actuals vs Cumulative Forecast.

2 ACCEPTED SOLUTIONS
Super User

try:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Transposed Table" = Table.Transpose(Source),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Actuals", Int64.Type}, {"Forecast", Int64.Type}, {"Cumulative Actuals", Int64.Type}}),
#"RT Forecast" =
Table.FromColumns(
Table.ToColumns(#"Changed Type1") &
{List.Generate(
()=>[rt=if #"Changed Type1"[Actuals]{1} = null
then #"Changed Type1"[Cumulative Actuals]{0}
else null,
idx=0],
each [idx] < Table.RowCount(#"Changed Type1"),
each [rt= if #"Changed Type1"[Actuals]{[idx]+2}? = null
then #"Changed Type1"[Cumulative Actuals]{[idx]+1}??[rt]  + #"Changed Type1"[Forecast]{[idx]+1}
else null, idx = [idx]+1],
each [rt])}, {"Actual","Forecast","Cumulative Actuals","Cumulative Forecast"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
#"Transposed Table1" = Table.Transpose(#"Changed Type2"),
#"Changed Type" = Table.TransformColumnTypes(#"Transposed Table1", {{"Column1", type text}} &
List.Zip({List.Skip(Table.ColumnNames(#"Transposed Table1")), List.Repeat({Int64.Type}, Table.ColumnCount(#"Transposed Table1")-1)})),
#"Rename Columns" = Table.RenameColumns(#"Changed Type", List.Zip({Table.ColumnNames(#"Changed Type"),Table.ColumnNames(Source)}))
in
#"Rename Columns"``````

Super User

To adapt the code I provided, you would delete the hard-coded table and change your Source step so that it returns a table in the same format (three rows of data) as you showed in your original question.

If you do that and are still getting errors, you will need to provide data in a format that is usable and reproduces the error for me to help. A screenshot is NOT usable. And in your screenshots, you show neither the error message nor identify the step that is returning that error.

5 REPLIES 5
Super User

try:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Transposed Table" = Table.Transpose(Source),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Actuals", Int64.Type}, {"Forecast", Int64.Type}, {"Cumulative Actuals", Int64.Type}}),
#"RT Forecast" =
Table.FromColumns(
Table.ToColumns(#"Changed Type1") &
{List.Generate(
()=>[rt=if #"Changed Type1"[Actuals]{1} = null
then #"Changed Type1"[Cumulative Actuals]{0}
else null,
idx=0],
each [idx] < Table.RowCount(#"Changed Type1"),
each [rt= if #"Changed Type1"[Actuals]{[idx]+2}? = null
then #"Changed Type1"[Cumulative Actuals]{[idx]+1}??[rt]  + #"Changed Type1"[Forecast]{[idx]+1}
else null, idx = [idx]+1],
each [rt])}, {"Actual","Forecast","Cumulative Actuals","Cumulative Forecast"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
#"Transposed Table1" = Table.Transpose(#"Changed Type2"),
#"Changed Type" = Table.TransformColumnTypes(#"Transposed Table1", {{"Column1", type text}} &
List.Zip({List.Skip(Table.ColumnNames(#"Transposed Table1")), List.Repeat({Int64.Type}, Table.ColumnCount(#"Transposed Table1")-1)})),
#"Rename Columns" = Table.RenameColumns(#"Changed Type", List.Zip({Table.ColumnNames(#"Changed Type"),Table.ColumnNames(Source)}))
in
#"Rename Columns"``````

Helper I

Hi @ronrsnfld ,

Thanks for the solution. It works for me. Can we Actuals,Forecast as bars and cumulative actuals and cumulative forecast as line graph in single visual?

Super User

Something like this?

That is a "Line and Clustered Column Chart" and the easiest way is to

- Start at the step #"RT Forecast" where the data is in columns

- Add an Index column (else the x-axis will be sorted alphabetically)

``````let
colNames={"Rows"} & List.Transform(List.Accumulate({1..11},{#date(2024,3,1)},(s,c)=>s & {Date.AddMonths(#date(2024,3,1),c)}), each Date.ToText(_,"MMM-yy")),
values = {{"Actuals","Forecast","Cumulative Actuals"},
{80,35,80},{65,25,145},{90,10,235},{95,20,330},{null,25,null},{null,30,null},{null,20,null},{null,35,null},{null,25,null},{null,40,null},{null,25,null},{null,40,null}},
Source = Table.FromColumns(values,colNames),
#"Transposed Table" = Table.Transpose(Source),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Actuals", Int64.Type}, {"Forecast", Int64.Type}, {"Cumulative Actuals", Int64.Type}}),
#"RT Forecast" =
Table.FromColumns(
Table.ToColumns(#"Changed Type1") &
{List.Generate(
()=>[rt=if #"Changed Type1"[Actuals]{1} = null
then #"Changed Type1"[Cumulative Actuals]{0}
else null,
idx=0],
each [idx] < Table.RowCount(#"Changed Type1"),
each [rt= if #"Changed Type1"[Actuals]{[idx]+2}? = null
then #"Changed Type1"[Cumulative Actuals]{[idx]+1}??[rt]  + #"Changed Type1"[Forecast]{[idx]+1}
else null, idx = [idx]+1],
each [rt])}, {"Actual","Forecast","Cumulative Actuals","Cumulative Forecast"}),

{List.RemoveFirstN(colNames,1)} &
Table.ToColumns(#"RT Forecast"),
type table[Date=text,Actual=Int64.Type,Forecast=Int64.Type, Cumulative Actuals=Int64.Type, Cumulative Forecast=Int64.Type]
),
in

Then select the chart type I mentioned above.

Drag the Date column to the x axis

Drag the appropriate columns to the  "Column y-axis" and "Line y-axis"

Sort the x-axis by the index column

Helper I

Hi @ronrsnfld,

Thankyou for the detailed solution. I am getting Error while replicating the above source code. In the above soulution you provided, the columns and values should not be hard coded. That will come dynamically. I have appended/combined two tables(Table1.has (actuals & cumulative actuals) and Table2. has(forecast values)

when i tried to replicate your same code it is throwing below error.

and the following output cumulative Forecast not showing as expected.

Please suggest me modification in code. Also please share sample pbix so that it make me easy.

Super User

To adapt the code I provided, you would delete the hard-coded table and change your Source step so that it returns a table in the same format (three rows of data) as you showed in your original question.

If you do that and are still getting errors, you will need to provide data in a format that is usable and reproduces the error for me to help. A screenshot is NOT usable. And in your screenshots, you show neither the error message nor identify the step that is returning that error.