cancel
Showing results for
Did you mean:
Highlighted
Helper II

## workaround for recursive calculation of my dax measure?

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?

7 REPLIES 7
Highlighted
Super User IV

@powerbihelp1245 - 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])

``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper II

(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)

Highlighted
Helper II

@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

Highlighted
Super User IV

@powerbihelp1245 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?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper II

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

 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.

Highlighted
Microsoft

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.

Highlighted
Helper II

let me test it. I will get back to you

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors