0

I wanted to calculate the column RestOrder in DAX. Can you guys please help? The TotalOrderDay column was calculated with the Allexcept() formula. I need something like calculate the orders for Statedate remaining or so...or a SQL "rownumber over partition by kinda thing".

https://i.stack.imgur.com/wRbhB.jpg

Thanks for help!

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
Andy
  • 11

1 Answers1

1

The following DAX can do:

RestOrder = 
COUNTROWS(
    FILTER(
        'Order',
        'Order'[StateDate].[Date] = EARLIER('Order'[StateDate].[Date]) &&
        'Order'[StateDate] >= EARLIER('Order'[StateDate])
    )
)

Results:

results

So what you're trying to achieve is to count the number of orders, which are of the same date but is earlier than others.

In order to achieve so, your Statedate has to be in Datetime type.

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
  • Hi @andy does this help solving the issue after all? If so, you can [accept this answer](https://meta.stackexchange.com/a/5235/313506) to positively close the question. – Foxan Ng Dec 22 '17 at 05:44