DAX formula for measure - recursive

[yfquirogah]

I'm trying to create a new measure to calculate the estimated amount of people a company has with the effect of a turn over forecast.

The amount of withdraws for a month x would be HC(x-1) * Forecast(x-1) and the new HC would be HC(x-1) - withdraws(x)

Salidas_Vol = ROUND(CALCULATE(SUM(Resumen[HC])*SUM(Forecasted_valuespbi[Point Forecast]);
PREVIOUSMONTH(Forecasted_valuespbi[rn]));0)

HC-Salidas_Vol = CALCULATE(SUM(Resumen[HC]);PREVIOUSMONTH(Forecasted_valuespbi[rn])) - [Salidas_Vol]

In 2021 / 11 the HC would now be 949, which will make the the expected withdraws for 2021 / 12 = 949 * 0,0023 which rounds to 2, that way the new HC is 947, and so on:

Any idea on how to get to this?

[lbendlin]

While DAX cannot do anything recursive (outside of PATH) you can achieve this with an iterator, specifically PRODUCTX().

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[yfquirogah]

This would be the expected result for the forecasted months:

1/10/2021	954	0.0051	4	947
1/11/2021	949	0.0023	5	949
1/12/2021	947	0.0024	2	947
1/01/2022	945	0.0021	2	945
1/02/2022	943	0.0033	2	943
1/03/2022	940	0.0075	3	940
1/04/2022	933	0.0016	7	933
1/05/2022	932	0.0012	1	932
1/06/2022	931	0.0005	1	931
1/07/2022	931	0.0031	0	931
1/08/2022	928	0.0041	3	928
1/09/2022	924	0.0037	4	924
1/10/2022	921	0.0051	3	921
1/11/2022	916	0.0023	5	916
1/12/2022	914	0.0024	2	914

I could not attach the documents nor the pbix file but you can see the excel file with the formula that I would apply here:

https://docs.google.com/spreadsheets/d/1qYcAWGaObZTCfttHT9Se9bGpc2vWWJ09/edit?usp=sharing&ouid=10245...

and the pbix file here:

https://drive.google.com/file/d/1WBZfGO3oP76617jcMgXYXlPlHHQv7fGH/view?usp=sharing

[lbendlin]

So I mentioned PRODUCTX().  Normally this would be the formula

NewHC = 
var m = [Month]
return 954 * PRODUCTX(Filter('Table',[Month]<m),1-[Point Forecast])

But you threw in the rounding part - that was a bit unexpected.  As a result the numbers are a bit off at the end

I'm still trying to figure out how to solve for that.

Edit: Power Query has a bit more sophisticated functionality for this using List.Accumulate()

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFbCoQwDAXQrYjfMuZVq2sR97+NSTO5GmE+SsPlkFhznjOvzKuQ8LzM03TY4Td9iES9aH5GdC0BpcIOaF5IwJ6QoqMkbIB8wwYoFWpC1RsqoFZICftorQEJ0ApUdOTNi/HBI0rYKhTAUXBAAdwq5ITUbsiA/R/8FW+4Fyh7QmM8xqOER4WGjuMhFtCwGaoQo9vTEaO5bib+ynvXHtVdA2L0s2uPrusL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1/10/2021" = _t, #"  954" = _t, #"0.0051" = _t, #"4" = _t, #"947" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Renamed Columns" = Table.RenameColumns(#"Demoted Headers",{{"Column1", "Month"}, {"Column2", "HC"}, {"Column3", "Point Forecast"}, {"Column4", "Salidas_Vol"}, {"Column5", "HC-Salidas_Vol"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Month", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"HC", Int64.Type}, {"Point Forecast", type number}, {"Salidas_Vol", Int64.Type}, {"HC-Salidas_Vol", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Month", "HC", "Point Forecast"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate({0..[Index]-1},954,(state,current)=>Number.Round(state*(1-#"Removed Other Columns"[Point Forecast]{current}))))
in
    #"Added Custom"

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".