New/Removed Rows Day-Over-Day

[en_90]

Hello, 

I would like to identify and count the changes in sales orders day-over-day i.e. identify/count the number of sales orders that were added and removed day-over-day so ideally, when the data is refreshed, I am comparing and summarizing the orders that were added and removed from the previous day to the current day. 

For example, based on the below table, RUN_DATE = 10/29, SALES_ORDER = 0205902208-000100 should be categorized as NEW because it's present for 10/29 but not for 10/28. 

RUN_DATE = 10/29, SALES_ORDER = 0204644520-000100 should be unchanged, or categorized as "NO CHANGE" because it was present in both 10/28 and 10/29. 

RUN_DATE = 10/28, SALES_ORDER = 0208188033-000200 should have been categorized as NEW when the data was ran on 10/28 but the categoriziation should be updated to REMOVED , when the data is refreshed on 10/29, because it is no longer present in 10/29. 

The below table is just an example of the logic described above and my current data set only has the RUN_DATE and SALES_ORDER columns. The output does not have to match the below table 

I am hoping to be able to accomplish this through DAX. 

Any help is appreciated! 

[v-xingshen-msft]

Hi ALL,
Firstly  GilbertQ thank you for your solution!
And @en_90 ,According to your needs, we use countrows to determine whether the previous date exists in the latest date, if it exists NO Change If it does not exist, then it is New , I hope that my thinking for you!

NO or Change = 
VAR PreviousDate2 = 
CALCULATE(MAX('Table'[RUN_DATE]), 
    FILTER(ALL('Table'), 'Table'[RUN_DATE] < MAX('Table'[RUN_DATE]))
)
RETURN
IF(
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[SALES_ORDER]=MAX('Table'[SALES_ORDER]),
        'Table'[RUN_DATE]=PreviousDate2)>0,
        "NO CHANGE",
        "NEW")

Remove = 
VAR CurrentDate =CALCULATE(MAX( 'Table'[RUN_DATE]),ALL('Table'))

RETURN 
IF(CALCULATE(
    COUNTROWS('Table'),
    'Table'[SALES_ORDER]=MAX('Table'[SALES_ORDER]),
    'Table'[RUN_DATE]=CurrentDate)>0,BLANK(),
    "REMOVE")

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[en_90]

@v-xingshen-msft thanks for the quick response. I have succesfully implemented your code but I need to be able to count the number of sales ordres that are new, no change and removed by RUN_DATE. WIth this code, I am unable to accomplish this. Any suggestions? Thank you. 

[GilbertQ]

Hi @en_90 

The pattern you are looking for is new and existing customers. Here is an article from Sql BI, which will explain the concept to you with DAX examples New and returning customers – DAX Patterns