topic Re: Need help with dynamic sum of open order values in DAX Commands and Tips

Need help with dynamic sum of open order values

Anonymous — Tue, 15 Dec 2020 12:35:46 GMT

Hi,

I have an issue which I cannot make it dynamic. I have to calculate sum of Quantity and Order value for open orders based on the date selected in the slicer. Any help is much appreciated! 🙂

Below is the sample data :-

OrderNo	Status	Productkey	OrderDate	OrderChangeDate	Quantity	Value
100	Open	2	20201005	20201005	10	5000
100	Invoiced	2	20201005	20201114	0	0
101	Open	5	20201009	20201009	20	1200
101	Open	5	20201009	20201114	20	1200
101	Invoiced	5	20201009	20201204	0	0

Result of measure

Date(is dynamic from the slicer selection)	Measure_Qty	Measure_Value
20201009	30	6200	(Both 100 and 101 are open)
20201114	20	1200	(Only 101 is open)
20201204	0	0	(No orders are open)

Re: Need help with dynamic sum of open order values

Anonymous — Thu, 17 Dec 2020 09:23:07 GMT

I don't see any 20201009 under order 100 in your source data. sth is contradictory here? Would be nice if you were specific on the date column in the slicer too.

If I read your intentions well, this should work:

Measure_value = CALCULATE( SUM('Table'[Value]), 'Table'[Status] = "Open" ) Measure_qty = CALCULATE( SUM('Table'[Quantity]), 'Table'[Status] = "Open" )

Best,

Pawel

Re: Need help with dynamic sum of open order values

Anonymous — Fri, 18 Dec 2020 11:13:15 GMT

Hi,

Maybe I should explain better.

If data selection on slicer is date 20201009 so that date both orders 100 and 101 are Open and not invoiced so the order value will be the sum of both orders.

Order number 100 was invoiced on 20201114. So on 20201114 only value for open order 101 will be shown.

The measure you mentioned would not work as I select the date from slicer and based on that all order which are open till that date should be summarized and showed in the measure value.

Best Regards,