Recursive calculated column

[mohammadjavaher]

Hi all.

I need to create a calculated column using a recursive approach. This is my current code (which of course doesn't work):

ForecastWeightAvg =

VAR pid = PeriodForecast[PERIODID]

VAR cat = PeriodForecast[CATALOGID]

VAR mfca = CALCULATE(MIN(PeriodForecast[ForecastAge]), PeriodForecast[PERIODID] = pid)

VAR cfca = PeriodForecast[ForecastAge]

VAR lqty = CALCULATE(SUM(GSLSPERIODPRODTABLE[QTY]), GSLSPERIODPRODTABLE[PERIODID] = pid)

VAR keep = 1 - (CALCULATE(MAX(GSLSBIRDCATALOGLINE[LOSSPCT]), GSLSBIRDCATALOGLINE[CATALOGID] = cat, GSLSBIRDCATALOGLINE[AGE] = cfca - 1) / 100)

VAR qty = IF(PeriodForecast[QTYSTART] = 0, PeriodForecast[QTYPLAN], IF(ISBLANK(lqty) || (lqty = 0), PeriodForecast[QTYSTART], lqty * keep))

VAR cqty = IF(cfca = mfca, qty, CALCULATE(MAX(PeriodForecast[ForecastWeightAvg]), PeriodForecast[PERIODID] = pid && PeriodForecast[ForecastAge] = cfca - 1) * keep)

RETURN cqty

As you know, Power BI won't allow part of formula highlighted in red. I have seen some solutions for sales forecast while googling, but I'm unable to match their solutions with my case, as I use values from other tables which make this case somehow different. Can someone help?

Regards,

Mohammad

[amitchandak]

@mohammadjavaher , power query you have some option : https://radacad.com/fibonacci-sequence-understanding-the-power-query-recursive-function

refer some DAX solution

https://forum.enterprisedna.co/t/dax-challenge-recursive/9091

[mohammadjavaher]

Thanks @amitchandak for your response. Bad news is that my table is a cross join between two other tables one of which is totally created by DAX (GENERATESERIES) and the other is a calculated table based on another table. I cannot take this joint table to Power Query, AFAIK. So I need a pure-DAX way to accomplish this.

Regards,

Mohammad