cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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 - 

KEYOrder valid till Dateorder quantityIn 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)
122-09-2020103003010
123-09-20201530102015
124-09-202020302555

 

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 - 

KEYOrder valid till Dateorder quantity
122-09-202010
123-09-202015
124-09-202020


in transit table - 

Keyexpected date of reachingQuanity in shipment
121-09-202030
125-09-202040


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


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




Highlighted

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

 

KEYOrder valid till Dateorder quantityIn 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)
122-09-2020103003010
123-09-20202530102020
124-09-2020203035-5 (?)-5

 

whereas it actually should show up like this - 

KEYOrder valid till Dateorder quantityIn 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)
122-09-2020103003010
123-09-20202530102020
124-09-202020303000



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

@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

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


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




Highlighted

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

KEYOrder valid till Date order quantity
122-09-202010
123-09-202025
124-09-202020

 

Keyexpected date of reaching Quanity in shipment
121-09-202030
124-09-202020

 

expected output - 

KEYOrder valid till Date order quantity total_qty used_stock avail_stock serviced 
122-09-2020103003010
123-09-20202530102020
124-09-20202050302020

 

your measures output - 

 

KEYOrder valid till Date order quantity total_qty used_stock avail_stock serviced
122-09-2020103003010
123-09-20202530102020
124-09-20202050351515

 

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
Microsoft

Hi @powerbihelp1245 ,

 

I used the following Sample data.

order table:

KEYOrder valid till Dateorder quantity

19/22/202010
19/23/202025
19/24/202020

 

in transit table:

KeyQuanity in shipmentexpected date of reaching

1309/21/2020
120

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.

5.png

 

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

let me test it. I will get back to you

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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