cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohammadjavaher
Regular 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
amitchandak
Super User
Super User

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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors