Forum Discussion
DAX formula for measure - recursive
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?
3 Replies
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.
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:
and the pbix file here:
https://drive.google.com/file/d/1WBZfGO3oP76617jcMgXYXlPlHHQv7fGH/view?usp=sharing
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".
