Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mohammadjavaher
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,
Mohammad
2 REPLIES 2

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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