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 ...
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 )
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
Community Champion
Community ChampionYou 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
)
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 )