Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
edwardvdm
Frequent Visitor

Reference the previous row's value in the same column of a calculated column or measure

Hi all, 

 

I have been trying all sort of tricks with DAX, but just cannot get to a solution to my challenge. Would appreciate some help. 

 

I have quite a practical example where I need to build an inventory planning model. Everything is working, except the aspect where I have to refer to my previous days stock on hand to calculate today's stock on hand. This was easy in Excel, but I cannot replicate in Power BI. 

 

I have tried functions like EARLIER, DATEADD with no luck. 


In Power BI I need to do the below: 

In cell E3 I need to reference cell E2 and minus sales + Place PO

 
 BCDEFG
 DateSales SOH BeginPlace POSOH + Open POPO ArrivalSOH
12019/01/0110 010 10
22019/01/025 010 10
32019/01/034 010010
42019/01/042 010010
52019/01/053 010010
62019/01/0612 010010
72019/01/074 0606
82019/01/0815 0-90-9
92019/01/0926 10-250-35
102019/01/1034 26-330-59
112019/01/1125 34-2410-48
122019/01/1213 25-1226-11
132019/01/1314 13-13348

 

Looking forward to a response. 

6 REPLIES 6
v-lid-msft
Community Support
Community Support

Hi @edwardvdm ,

 

We can try to create calculated column with following formula to meet your requirement:

 

ColumnE =
CALCULATE (
    SUM ( 'Table'[Place PO] ) - SUM ( 'Table'[Sales] ),
    FILTER ( 'Table', 'Table'[Date] <= EARLIER ( [Date] ) )
)

 

But in your sample table, the E2 = 10, sales = 4 and place po = 0, the E3 should be 10-4+0=6, but in your table, the E3 = 10, So If the formula doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your response, maybe it will make a bit more sense when I outline the model I created in Excel. This will also explain better my expected result. 

 

In Excel, this was quite easy. But when I implement in Power BI I create Circular dependencies. In Excel, I have my sales column that is calculated from actual sales. I have my SOH column (beginning SOH) that refers to the previous day's (SOH+OPEN PO's) Column. I have my (SOH+OPEN PO's) column that calculates from SOH- Sales + PO Placed. Lastly, I have my PO Place column that refers to my (SOH+OPEN PO's) column to calculate if a PO must get generated or not. The problem is the dependence of my PO Placed and SOH + OPEN PO's.

 

DateSales SOHSOH + Open POPO PlacedTotalTotal to Yellow TopSOHSOH + Open POPO Placed
2019/02/0967393269978   
2019/02/1017937609473Equals I2Equals C2-B2+E2Equals if(D2<L2,K2-D2,0)
2019/02/1120765609473   
2019/02/12245670379473   
2019/02/13147085309978   
2019/02/1415857009473   
2019/02/15197075239473   
2019/02/1611756409473   
2019/02/17316463309473   
2019/02/18366358319473   
2019/02/19235871369473   
2019/02/20467148239473   
2019/02/21264873529978   

 

Hope this is more clear. 

Hi @edwardvdm ,

 

Circular Dependencies does not support in Power BI, But if your PO Placed column is constant, we can create SOH and SOH + Open PO use my previous formula. By the way, could you please explain the L2 and K2, we can guess the D2 is SOH + Open PO.

 

9.PNG

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you I have added the Columns alphabetically and row numbers: 

 

The trick comes in that I calculate my PO Placed from my SOH column in Excel and that creates the circular dependency.  

 

 ABCDEFGColumn C FormulaColumn D FormulaColumn E Formula
 DateSales SOHSOH + Open POPO PlacedTotalTotal to Yellow TopSOHSOH + Open POPO Placed
12019/02/0967393269978   
22019/02/1017937609473Equals D1Equals C2-B2+E2Equals if(D2<L2,K2-D2,0)
32019/02/1120765609473   
42019/02/12245670379473   
52019/02/13147085309978   
62019/02/1415857009473   
72019/02/15197075239473   
82019/02/1611756409473   
92019/02/17316463309473   
102019/02/18366358319473   
112019/02/19235871369473   
122019/02/20467148239473   
132019/02/21264873529978   

Hi @edwardvdm ,

 

Thanks for marking the row and colmun numbers, we have understand the logic of Column C, D, E except for the meaning of L2 and D2, does they constant? Could you please provided the sample of column L and K? We may could try to use formula to calculate the column D and E without using circular dependency.

 

SOH = "SOH + Open PO" of previous day
SOH + Open PO = "SOH + Open PO" of previous day - Sales of current day + "PO Placed" of current day

PO Placed = Column K minus "SOH + Open PO" if "SOH + Open PO" less than Column L, otherwise zero

 

Please also look at the row 7 in your shared table, does the "SOH + Open PO" should be 70-19+23 = 74 instead of 75?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lid-msft 

Thanks for the responses so far. 

L2 stays constant (not influencing the formulas) and D2 is dynamically calculated as explained in the previous post. Thus L and K do not affect the formulas as circular dependency. 

 

If it might help, I can share the Excel file and Power BI file, that might just be better to get and understanding of what is required?

 

The solution that I have in Power BI is as follow:

 

I get the circular dependency in my SOH measure. 

 

Sales M = fixed number

 

Place PO Test =
var a = CALCULATE(SUM('Master Table'[Index])) - 1
Return
ROUNDUP(IF(CALCULATE([SOH + Open PO Test]<[Top of Yellow],ALLNOBLANKROW('Master Table'),'Master Table'[Index]= a),[Total Zone]-[SOH],0),0)
 
SOH + Open PO Test =
var a = CALCULATE(SUM('Master Table'[Index])) - 1
Return
[Place PO]-[Sales M]+CALCULATE([SOH],ALLNOBLANKROW('Master Table'),'Master Table'[Index]= a)
 
SOH =
var a = CALCULATE(SUM('Master Table'[Index])) - 1
Return
CALCULATE([SOH + Open PO Test], ALLNOBLANKROW('Master Table'),'Master Table'[Index]= a)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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