workaround for recursive calculation of my dax measure?

[Anonymous]

Hi, 

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 - 

KEY	Order valid till Date	order quantity	In transit stock reaching before this date(total_qty)	cumulative orders covered before this date(used_stock)	Stock reached which has not been used up(avail_stock)	order quantity of this date serviced (serviced)
1	22-09-2020	10	30	0	30	10
1	23-09-2020	15	30	10	20	15
1	24-09-2020	20	30	25	5	5

I 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 - 

KEY	Order valid till Date	order quantity
1	22-09-2020	10
1	23-09-2020	15
1	24-09-2020	20

in transit table - 

Key	expected date of reaching	Quanity in shipment
1	21-09-2020	30
1	25-09-2020	40

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?

[v-stephen-msft]

Hi @Anonymous ,

I used the following Sample data.

order table:

KEYOrder valid till Dateorder quantity

1	9/22/2020	10
1	9/23/2020	25
1	9/24/2020	20

in transit table:

KeyQuanity in shipmentexpected date of reaching

1	30	9/21/2020
1	20	9/24/2020

Create these measures.

total_qty = 
CALCULATE (
    SUM ( 'in transit table'[Quanity in shipment] ),
    FILTER (
         'in transit table' ,
        [expected date of reaching] <= MAX ( 'order table'[Order valid till Date] )
    )
)

used_stock = 
VAR _Calc =
    CALCULATE (
        SUM ( 'order table'[order quantity] ),
        FILTER (
            ALL ( 'order table' ),
            [Order valid till Date] < MAX ( 'order table'[Order valid till Date] )
        )
    )
VAR _Quanity =
    CALCULATE (
        SUM ( 'in transit table'[Quanity in shipment] ),
        FILTER (
             'in transit table' ,
            [expected date of reaching] < MAX ( 'order table'[Order valid till Date] )
        )
    )
RETURN
    IF ( _Calc + 0 > _Quanity, _Quanity, _Calc + 0 )

avail_stock = 
[total_qty] - [used_stock]

serviced = 
MIN ( MAX ( 'order table'[order quantity] ), [avail_stock] )

The result is this.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

let me test it. I will get back to you

[Greg_Deckler]

@Anonymous - See attached PBIX below sig, Table 2, 2a and 2b. On Page 2. I created the following measures:

total_qty = SUMX(FILTER(RELATEDTABLE('Table (2a)'),[expected date of reaching]<MAX([Order valid till Date])),[Quanity in shipment])


used_stock = 
    VAR __Date = MAX([Order valid till Date])
    VAR __Calc = SUMX(FILTER(ALL('Table (2)'),[Order valid till Date]<__Date),[order quantity])
RETURN
    __Calc+0


avail_stock = [total_qty] - [used_stock]


serviced = MIN(MAX([order quantity]),[avail_stock])

[Anonymous]

@Greg_Deckler thanks a lot for your reply.

(edit 2 - in short, used stock should be sum of serviced rather than sum of orders which will result in circular dependency)

But, these measures will only give me the correct result for this data in which orders were less than the available stock. If the order becomes greater than available stock, your sumx for calculating used stock will be wrong as the used stock should only be sum of min(order,availble stock) and not just sum of order quanitity as used_stock can't be greater than available stock.

For example if the data had 2nd order as 25 instead of 15, your mesures will show - 

KEY	Order valid till Date	order quantity	In transit stock reaching before this date(total_qty)	cumulative orders covered before this date(used_stock)	Stock reached which has not been used up(avail_stock)	order quantity of this date serviced (serviced)
1	22-09-2020	10	30	0	30	10
1	23-09-2020	25	30	10	20	20
1	24-09-2020	20	30	35	-5 (?)	-5

whereas it actually should show up like this - 

KEY	Order valid till Date	order quantity	In transit stock reaching before this date(total_qty)	cumulative orders covered before this date(used_stock)	Stock reached which has not been used up(avail_stock)	order quantity of this date serviced (serviced)
1	22-09-2020	10	30	0	30	10
1	23-09-2020	25	30	10	20	20
1	24-09-2020	20	30	30	0	0

Basically, the sumx on used stock should be on min(order quantity, avail_stock) but this results in circular dependency error. 

We might still handle these -5 by making it zero if the result is negative, but if a new shipment arrives on 24-09 it will mess up the avail_stock as then it will add a erronous -5.

(edit - Just fyi, orders don't carry forward, they are only valid till the validity date mentioned)

[Greg_Deckler]

@Anonymous Not clear on this. Seemed like I was getting all the right answers. Perhaps you could provide an expanded set of sample data and expected output?

[Anonymous]

See the following data and the expected output and the output your measures will give - 

KEY	Order valid till Date	order quantity
1	22-09-2020	10
1	23-09-2020	25
1	24-09-2020	20

Key	expected date of reaching	Quanity in shipment
1	21-09-2020	30
1	24-09-2020	20

expected output - 

KEY	Order valid till Date	order quantity	total_qty	used_stock	avail_stock	serviced
1	22-09-2020	10	30	0	30	10
1	23-09-2020	25	30	10	20	20
1	24-09-2020	20	50	30	20	20

your measures output - 

KEY	Order valid till Date	order quantity	total_qty	used_stock	avail_stock	serviced
1	22-09-2020	10	30	0	30	10
1	23-09-2020	25	30	10	20	20
1	24-09-2020	20	50	35	15	15

Again please note I only want correct calculation of the serviced measure, rest is just the intermediate logic built by me. So, I'm open to a different logic to get to the serviced column.

[Anonymous]

@Greg_Deckler 

for anyone trying to solve this, I just want the correct calculation of serviced column, the rest columns are intermediate created by me as logic. If there is other elegant logic to reach the serviced column, that would be brilliant as it looks like my logic will inevitably result in a circular dependency