Check if record exists in previous month

[imranamikhan]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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]

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 · Accepted solution]

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 · Accepted solution]

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")

[amitchandak]

@imranamikhan , refer my blog to check values for customer this month vs last month, see if that can help

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[imranamikhan]

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.