cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Super User
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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors