Forum Discussion

en_90's avatar
en_90
Frequent Visitor
1 year ago

New/Removed Rows Day-Over-Day

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! 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      en_90
      Frequent Visitor
      1 year ago

      Anonymous 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.