Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kkanukurthi
Helper I
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.

 

kkanukurthi_0-1718511922174.png

 

 

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

Note : Cumulative Forecast should not start with Forecast cell value.

 

Thanks in Advance

2 ACCEPTED SOLUTIONS
ronrsnfld
Super User
Super User

Given your input

ronrsnfld_0-1718537727266.png

 

try:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"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"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"RT 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"

 

ronrsnfld_1-1718537772082.png

 

 

 

 

View solution in original post

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.

View solution in original post

5 REPLIES 5
ronrsnfld
Super User
Super User

Given your input

ronrsnfld_0-1718537727266.png

 

try:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"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"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"RT 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"

 

ronrsnfld_1-1718537772082.png

 

 

 

 

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?

 

Something like this?

ronrsnfld_0-1718655987249.png

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 the Month/Year column

 - 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),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"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"}),
   
    #"Add Month Column" = Table.FromColumns(
        {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]
    ),
    #"Added Index" = Table.AddIndexColumn(#"Add Month Column", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

 

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

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)

Please check the code below.

kkanukurthi_0-1718808853678.png

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

kkanukurthi_1-1718809270014.png

and the following output cumulative Forecast not showing as expected.

kkanukurthi_0-1718811190004.png

 

 

 

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

 

Thanks in Advance.

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors