POWER BI - workaround possible for recursive calculation of my dax measure?

Question

I have 2 tables on orders with the order validity date and in transit stock (stock reaching where the order will be serviced).

(using sample data to simplify for understanding) I am looking for a final calculation like this in my final table -

have done the calculation till column 4 in power BI

if this was in excel i could have simply done

used_stock(2) = serviced(1) + used_stock(1)

avail_stock(2) = total_qty(2) - used_stock(2)

serviced(2) = min(order(2),avail_stock(2))

My base tables look like this -

order table -

intransit table -

I have done the total_qty measure calculation by finding the cumulative sum of shipment quantity for the dates before selected value of order validity date.

I am trying to do the rest of the measures but ending up in circular references. Is there a way I can do it?

edit -

Clarifying it a bit more for the logic needed -

let's say the 2nd order is 15 and the 2nd shipment reaches on 24th, then the base data and output table should look like this -

With present proposed solution the table will erroneously look like -

score 0 · Answer 1 · answered Sep 21 '20 at 11:50

0

Try with these below measures-

total_qty = 

VAR current_row_date = MIN('order'[order valid till date])

RETURN
CALCULATE(
    SUM(intrasit[quantity in shipment]),
    FILTER(
        ALL(intrasit),
        intrasit[expected date of reaching] < current_row_date
    )
)

used_stock = 

VAR current_row_date = MIN('order'[order valid till date])

RETURN
CALCULATE(
    SUM('order'[order quantity]),
    FILTER(
        ALL('order'),
        'order'[order valid till date] < current_row_date
    )
) + 0

avail_stock = [total_qty] - [used_stock]

serviced = 
IF(
    MIN('order'[order quantity]) <= [avail_stock],
    MIN('order'[order quantity]),
    [avail_stock]
)

Here is your final output-

answered Sep 21 '20 at 11:50

mkRabbani

16,295
2
15
24

thanks for your reply but shouldn't the used stock be sum of serviced rather than sum of orders. I had done something very similar but in used stock I was taking a sumx of min(order,avail_stock) which results in a circular dependency. Basically had the second order been 25 instead of 15 it will give used stock as 35 in 3rd row which actually should still be 30. This is important because if a new shipment arrives on let's say 24th sept of 20 units, the available stock will become 50-35 rather than 50-30 which can give erroneous result for serviced. – Chaitanya Dhawan Sep 21 '20 at 13:30
Update your required output with explaining logic. – mkRabbani Sep 21 '20 at 13:32
hey, have updated the post with an edit, please check. Really hope you can help me out. – Chaitanya Dhawan Sep 21 '20 at 13:55
As you said used stock should be sum of serviced, this is creating a circular dependency like one measure depend on another which is not created yet. You can check your logic once again. I am also checking your updated Question now. – mkRabbani Sep 21 '20 at 13:58
You "used stock" depends on "services" and "services" depends on "used stock" - which is not possible to calculate and this is the issue belongs to Circular Dependency :( – mkRabbani Sep 21 '20 at 14:07
I agree and this is the step I was getting stuck in unfortunately. Is there a workaround we can use? Some use of dateadd function or some fancy way of writing the recursion as a closed loop formula like mentioned [here](https://stackoverflow.com/questions/60641059/dax-formula-referencing-itself) ? If not, can you suggest what other data handling service should I use to handle this kind of problem? I've also seen some examples of power query handling recursion. – Chaitanya Dhawan Sep 21 '20 at 14:28
As per my knowledge, not possible :( you have to recheck your requirement. This is not logical as well. – mkRabbani Sep 21 '20 at 14:54
why do you say it is not logical? Also, I just want the correct calculation of last serviced column, others are just intermediate column which I am using to arrive at the serviced column – Chaitanya Dhawan Sep 22 '20 at 05:34
You are building 2 Car A and B for say. Now condition is - Process for Car A will start after end of process Car B. And Process for Car B will start after end of process Car A. Is that logical? – mkRabbani Sep 22 '20 at 05:42
did you just say the whole concept of recursion is illogical? – Chaitanya Dhawan Sep 22 '20 at 06:05
Nope, this logic you can not compare with recursion completely. Recursion never have this type of circular dependency. – mkRabbani Sep 22 '20 at 06:08
how it is illogical dude, I have given a business example as well. The used stock is the stock which has been used up from the total stock which has arrived. and that used stock is the sum of stock which we have serviced before today. The amount of order which we will be able to service today will be then the (total stock - used stock). This is the bread and butter of excel dude, how can you say it is illogical. I can understand if it is not possible in power Bi but to say this is illogical is just crass – Chaitanya Dhawan Sep 22 '20 at 06:17
You case is not recursion :( – mkRabbani Sep 22 '20 at 06:25
excel also show same error for your case. check the image - https://ibb.co/cJpD407 – mkRabbani Sep 22 '20 at 06:52
see my implementation in excel - [final table](https://imgur.com/a/BYoujfY) [with formulas](https://imgur.com/zCseqBi) – Chaitanya Dhawan Sep 22 '20 at 07:54
have edited in the with formula excel table as well in above message – Chaitanya Dhawan Sep 22 '20 at 07:56

POWER BI - workaround possible for recursive calculation of my dax measure?

1 Answers1