cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Pdecis Frequent Visitor
Frequent Visitor

Recursive Measure PowerBI

Hi All,

 

I would like to calcul : Stock Month -1+ Entrées Prév - Sorties Prév for each row(row = month)

The Column "Sorties Prev" is a measure in link with few measures.
I add a measure : ForecastCalculation =
VAR PrevInv = CALCULATE(SUMX('Simulation StockDP (2)'; 'Simulation StockDP (2)'[Stock]); DATEADD(DateTable[Date]; -1; MONTH))
RETURN (PrevInv + SUMX('Simulation StockDP (2)'; [Entrées Prév - Sorties Prév]))
The line for February is good, but for March the resulat is note good because the stock egals 0.
For March, i would like to calculate : Stock February + Entrées Prev - Sorties Prév (6673+2576-2693)
For April, i would like to calculate : Stock March+ Entrées Prev - Sorties Prév (6556+1730-2474)

For May, ....

PBI_Community.png

 

Please , help me.

Thanks a lot.

5 REPLIES 5
Super User
Super User

Re: Recursive Mesure PowerBI

DAX does not generally like recursion. See the article:

 

https://community.powerbi.com/t5/Community-Blog/Runge-Kutta-and-the-Limits-of-DAX/ba-p/357501

 

However, I wrote a blog article recently that might help with your situation:

 

https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314

 

Could you post sample data as text and expected output? Would help tremendously.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Pdecis Frequent Visitor
Frequent Visitor

Re: Recursive Mesure PowerBI

I would like this :
Stock = 7316 (january)
Stock for February = Stock January (7316)+Entrées-Sorties
Stock for March = Stock February + Entrées + Sorties

In Power BI, Sorties ia a measure link with few measures.
....

 

 

 

PBI_Community2_EXCEL.png

 

 

 

 



Thanks

Super User
Super User

Re: Recursive Mesure PowerBI

Hmm, well I gave this a lot of thought. Not sure I have what I need from a sample data perspective to give you a detailed solution, but I did come up with a way to emulate recursion in DAX that might work for your purposes. See here:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Previous-Value-aka-quot-Recursion-quot/m-p/6...

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Recursive Mesure PowerBI

While recursion is elegant from a coding point of view there are usually also non-recursive solutions for most problems.

 

I'm not sure if I understand your issue correctly, but it looks to me like you are trying to calculate stock on hand based on looking at the Incoming (Entrées) and Outgoing (Sortes) stock. If this is the case and your period is based off a Date column in your model you could do this by calculating a lifetime to date for Incoming and Outgoing and subtract one from the other.

 

eg

 

Stock = 
VAR _prevEntrées = SUMX( Filter('table1'[DateColumn], 'table1'[DateColumn] <= MAX('table1'[DateColumn]), [Entrées] )
VAR _prevSorties = SUMX( Filter('table1'[DateColumn], 'table1'[DateColumn] <= MAX('table1'[DateColumn]), [Sorties] )
RETURN _prevEntrées - _prevSorties

Pdecis Frequent Visitor
Frequent Visitor

Re: Recursive Mesure PowerBI

Hi,

My measure Forecast2  is good pour January 2019 but not good results for others month.
The measure "Sorties2" included several measures and What If Paramater (Tx de controle).




The result for the Measures "Sorties2" is good .

If i add "-Sorties2" in formula "Forecast2", the result is bad.

Why ?
Thanks for your help.

Pierre

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 107 members 1,592 guests
Please welcome our newest community members: