Forum Discussion

imranamikhan's avatar
imranamikhan
Icon for Helper V rankHelper V
5 years ago
Solved

Check if record exists in previous month

Hi everyone,

 

I have a table with an ID column and a Date/Timestamp column.

 

Could anyone advise a formula (either Power Query or DAX) to identify if a record exists in the same table but for the last day of the previous month against the timestamp for that record?

 

In Excel for example:

 

Criteria Range 1: Record ID column

Criteria1: Record ID

Criteria Range 2: Timestamp column

Criteria 2: Timestamp previous month

 

I would then check if the returned result is greater than > 0

 

=IF(COUNTIFS([Customer],[@Customer],[Record Timestamp],EOMONTH([@[Record Timestamp]],-1))>0,"Old","New")

 

 

best regards,

Ami

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi imranamikhan,

    So you mean you want to add an additional filter on custom field to further filter the records which already filtered by date ranges? 

    If this is a case, where are you wanted to add it? The variable of base filter step of the final result that processing with except functions?

    If you mean the second scenario to filter on final result, you can try to use the following formulas:

    New VM =
VAR customerlist =
    ALLSELECTED ( Table[Customer] )
VAR _history =
    CALCULATETABLE (
        VALUES ( DB_CustomerMasterData_Current_Report[Customer] ),
        FILTER (
            ALL ( DB_CustomerMasterData_Current_Report ),
            [Record Timestamp]
                < EARLIER ( DB_CustomerMasterData_Current_Report[Record Timestamp] )
        )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( DB_CustomerMasterData_Current_Report[Customer] ),
        FILTER (
            ALL ( DB_CustomerMasterData_Current_Report ),
            [Record Timestamp]
                = EARLIER ( DB_CustomerMasterData_Current_Report[Record Timestamp] )
        )
    )
VAR devices_found =
    COUNTROWS ( _current )
VAR devices_not_found =
    COUNTROWS ( EXCEPT ( _history, _current ) ) + 0
VAR new_devices =
    COUNTROWS (
        FILTER ( EXCEPT ( _current, _history ), [Customer] IN customerlist )
    ) + 0
RETURN
    new_devices

    Regards,

    Xiaoxin Sheng

  • imranamikhan's avatar
    imranamikhan
    5 years ago

    Hi Anonymous - apologies for the delayed response. The suggestion you provided was giving me a count but I I needed an additional column which identified whether a record was New (TRUE) or Old (FALSE) from a list of duplicate records with a timestamp (again per the image in my first post). 

    I now have the required DAX and I have posted it below in case anyone else needs this:

    Last date of previous month = EOMONTH(DB_CustomerMasterData_Current_Report[Record Timestamp],-1)

     

    New at Timestamp = if(CALCULATE(COUNTROWS(DB_CustomerMasterData_Current_Report),FILTER(DB_CustomerMasterData_Current_Report,DB_CustomerMasterData_Current_Report[CustomerID]=EARLIER(DB_CustomerMasterData_Current_Report[CustomerID])&&EOMONTH(DB_CustomerMasterData_Current_Report[Record Timestamp],0)=EARLIER(DB_CustomerMasterData_Current_Report[Last date of previous month])))>0,"Old","New")
     

6 Replies

    • imranamikhan's avatar
      imranamikhan
      Icon for Helper V rankHelper V
      5 years ago

      Hi amitchandak. This seems to work for the latest month (December) but I need the table to display all periods/months from the Record timestamp column rather than via a slicer because ultimately I want to build a visual.

       

       

      If I do use a slicer using the Date field from a joined Date calendar, the calculation is returning the count of all records for the previous month rather than new records. I also tried to use the Record timestamp field in the measures rather than the Date field from the Date calendar, and observed the same results.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi imranamikhan,

    According to your reading, it seems like history records analysis requirement.

    If this is a case, you can take a look at the following link if it meets your requirement: (I build the sample based on the filter and except functions, for your scenario, you only need to modify the filter range to the previous month)

    Get number and values of Virtual Machines created 
    Regards,

    Xiaoxin Sheng

    • imranamikhan's avatar
      imranamikhan
      Icon for Helper V rankHelper V
      5 years ago

      thanks Anonymous. This solution is certainly close to what I need but could you advise how I could modify this to return which records are new? Per the example image, I want to add a column to the list of customers and filter on which records are new.

      New VM = 
var _history=CALCULATETABLE(VALUES(DB_CustomerMasterData_Current_Report[Customer]),FILTER(ALL(DB_CustomerMasterData_Current_Report),[Record Timestamp]<EARLIER(DB_CustomerMasterData_Current_Report[Record Timestamp])))
var _current=CALCULATETABLE(VALUES(DB_CustomerMasterData_Current_Report[Customer]),FILTER(ALL(DB_CustomerMasterData_Current_Report),[Record Timestamp]=EARLIER(DB_CustomerMasterData_Current_Report[Record Timestamp])))
var devices_found=COUNTROWS(_current)
var devices_not_found=COUNTROWS(EXCEPT(_history,_current))+0
var new_devices=COUNTROWS(EXCEPT(_current,_history))+0
Return
new_devices
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi imranamikhan,

        So you mean you want to add an additional filter on custom field to further filter the records which already filtered by date ranges? 

        If this is a case, where are you wanted to add it? The variable of base filter step of the final result that processing with except functions?

        If you mean the second scenario to filter on final result, you can try to use the following formulas:

        New VM =
VAR customerlist =
    ALLSELECTED ( Table[Customer] )
VAR _history =
    CALCULATETABLE (
        VALUES ( DB_CustomerMasterData_Current_Report[Customer] ),
        FILTER (
            ALL ( DB_CustomerMasterData_Current_Report ),
            [Record Timestamp]
                < EARLIER ( DB_CustomerMasterData_Current_Report[Record Timestamp] )
        )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( DB_CustomerMasterData_Current_Report[Customer] ),
        FILTER (
            ALL ( DB_CustomerMasterData_Current_Report ),
            [Record Timestamp]
                = EARLIER ( DB_CustomerMasterData_Current_Report[Record Timestamp] )
        )
    )
VAR devices_found =
    COUNTROWS ( _current )
VAR devices_not_found =
    COUNTROWS ( EXCEPT ( _history, _current ) ) + 0
VAR new_devices =
    COUNTROWS (
        FILTER ( EXCEPT ( _current, _history ), [Customer] IN customerlist )
    ) + 0
RETURN
    new_devices

        Regards,

        Xiaoxin Sheng