cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors