cancel
Showing results for
Did you mean:
Frequent Visitor

## Recursive calculated column

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,
2 REPLIES 2
Frequent Visitor

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,

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors