Forum Discussion
Finding lost orders in a calculated column
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!
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 )
5 Replies
- Greg_Deckler
Community Champion
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)- Greg_Deckler
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 )