Distinct count of Orders with reversal orders

DAX DAX Calculations
1

Hi All,

I have a table with orders and one order has multiple items sold. The order number is unique. some orders are returned next month with minus sales value. I have a calculation for a Distinct count to get the number of orders. My calculation works perfectly for YTD count. but when I bring the month into context it obviously ignores sales returned next month. So monthly volume is not adding up correctly. I want to count orders by removing returned order, Returned orders should be adjusted on current month rather than the month of sales.

Please help.

Thanks

cjay

2

This is a really interesting one. Will just need a bit more time to test and think about a good solution here. Thanks

3

Thanks Sam.

4

Ok so I’ve located actually the exact tachnique that you will likely need to use here

It’s detailed here

There will be some slight adjustments to the formula but this is the idea of what you need.

This is what I think you will need

Order w/Return =
VAR CurrentOrders = VALUES( Orders Column )
VAR FutureOrders = CALCULATETABLE( Orders Column ), 
                        FILTER( ALL( Dates ),
                            Dates[Date] > MAX( Dates[Date] ) ) )

RETURN
COUNTROWS(
    EXCEPT( CurrentOrders, FutureOrders ) )

The idea here should intuitively work. I’ve thought quite deeply about it.

See how you go with this.

Chrs