Forum Discussion

yfquirogah's avatar
yfquirogah
Helper I
4 years ago

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) ...
Need Help
Show and Tell
lbendlin's avatar
lbendlin
Super User
4 years ago

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's avatar
yfquirogah
Helper I
4 years ago

This would be the expected result for the forecasted months:

 

1/10/2021  9540.00514947
1/11/2021  9490.00235949
1/12/2021  9470.00242947
1/01/2022  9450.00212945
1/02/2022  9430.00332943
1/03/2022  9400.00753940
1/04/2022  9330.00167933
1/05/2022  9320.00121932
1/06/2022  9310.00051931
1/07/2022  9310.00310931
1/08/2022  9280.00413928
1/09/2022  9240.00374924
1/10/2022  9210.00513921
1/11/2022  9160.00235916
1/12/2022  9140.00242914

 

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=102455755481370336169&rtpof=true&sd=true

and the pbix file here:

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

 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

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