Finding lost orders in a calculated column

[Anonymous]

Hi!

I am having trouble writing a DAX formula for a calculated column. In summary, my organization recieves a ton of orders everyday from different customers. It is possible that an order can be "open" for a few days before stock is confirmed and the order is shipped. Sometimes an order is dropped in the system which could happen for a variety of reasons. I am trying to track these orders that are dropped. Currently, I have a formula that works and uses the TODAY() function. I would like to use a new formula that eliminates the use of TODAY() so I can track historicals and weekly averages. An example of what I am looking for is below.

DATE	ORDER ID	LOST ORDER CHECK
1/1	123	0
1/1	456	1
1/1	789	0
1/2	123	0
1/2	789	0

I would like a 1 registered next to order 456 since it does not appear on 1/2, but appeared on 1/1. I would appreciate any help! Unfortunately, I cannot share any data as it is highly confidential. And again, I would like to eliminate my use of the TODAY() function, thanks in advance!

[Greg_Deckler]

Well, perhaps something like this:

LOST ORDER =
  VAR __OrderID = [ORDER ID]
  VAR __Date = [DATE]
  VAR __Table = FILTER('Table',[DATE] = __Date + 1.)
RETURN
  IF(__OrderID IN SELECTCOLUMNS(__Table,"OrderID",[ORDER ID]),0,1)

[Anonymous]

Seems as if this is working! Thank you very much!!! One more thing - it registers a 1 for all of today's order since there is no entries for tomorrow. Is it possible to manipulate this formula to exclude 1's from today's date? Thank you!!

[Greg_Deckler · Accepted solution]

You may probably try:

LOST ORDER =
  VAR __OrderID = [ORDER ID]
  VAR __Date = [DATE]
  VAR __Table = FILTER('Table',[DATE] = __Date + 1.)
RETURN
  SWITCH(TRUE(),
    __Date = TODAY(),0,
    __OrderID IN SELECTCOLUMNS(__Table,"OrderID",[ORDER ID]),0,
    1
  )

[Anonymous]

This is a huge help ... thank you very much!!! One last request if you have the time - can we exclude the last day of each month in the calculation? Orders every month get piled up until a new month where everything resets. So right now it is showing that I lose all of my orders at the beginning of each beginning of month / rest. Thank you again!!

[Greg_Deckler]

Let's see...

LOST ORDER =
  VAR __OrderID = [ORDER ID]
  VAR __Date = [DATE]
  VAR __Table = FILTER('Table',[DATE] = __Date + 1.)
RETURN
  SWITCH(TRUE(),
    __Date = TODAY(),0,
    __Date = EOMONTH(__Date,0),0,
    __OrderID IN SELECTCOLUMNS(__Table,"OrderID",[ORDER ID]),0,
    1
  )