Using Dax for Left anti-join in the same table

[salahy]

Hello,

I am new at DAX and I have issue,

I have table called transactions

which contains CustomerID and Date

I want to know who are the customers bought in the previous years but not in this year,

for example:

CustomerID  |  Date

1|  10/10/2021

1|  10/10/2021

2|  10/10/2019

2|  10/10/2019

2|  10/10/2012

3|  10/10/2020

1|  10/10/2020

3|  10/10/2019

1|  10/10/2020

I want to have this result

CustomerID

2

3

since they have transactions before 2021 and not in 2021

Thank you

[AntrikshSharma]

@salahy Add a Year column in your table or use a date table and then use this:

M =
VAR CurrentYear =
    CALCULATE (
        YEAR ( MAX ( Transactions[Date] ) ),
        REMOVEFILTERS ( Transactions )
    )
VAR CustomerPY =
    CALCULATETABLE (
        DISTINCT ( Transactions[CustomerID] ),
        Transactions[Transaction Year] < CurrentYear,
        REMOVEFILTERS ( Transactions )
    )
VAR CustomerCY =
    CALCULATETABLE (
        DISTINCT ( Transactions[CustomerID] ),
        Transactions[Transaction Year] = CurrentYear,
        REMOVEFILTERS ( Transactions )
    )
VAR OldCustomers =
    EXCEPT ( CustomerPY, CustomerCY )
VAR OldCustomerInFilterContext =
    FILTER (
        OldCustomers,
        Transactions[CustomerID] IN VALUES ( Transactions[CustomerID] )
    )
VAR Result =
    COUNTROWS ( OldCustomerInFilterContext )
RETURN
    Result

 or

M =
VAR CurrentYear =
    CALCULATE (
        YEAR ( MAX ( Transactions[Date] ) ),
        REMOVEFILTERS ( Transactions )
    )
VAR CustomerPY =
    CALCULATETABLE (
        DISTINCT ( Transactions[CustomerID] ),
        Transactions[Transaction Year] < CurrentYear,
        REMOVEFILTERS ( Transactions )
    )
VAR CustomerCY =
    CALCULATETABLE (
        DISTINCT ( Transactions[CustomerID] ),
        Transactions[Transaction Year] = CurrentYear,
        REMOVEFILTERS ( Transactions )
    )
VAR OldCustomers =
    EXCEPT ( CustomerPY, CustomerCY )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Transactions[CustomerID] ),
        KEEPFILTERS ( OldCustomers )
    )
RETURN
    Result

[Jos_Woolley]

Hi,

How about 2 measures:

Transactions 2021 =
CALCULATE ( COUNTROWS ( transactions ), YEAR ( transactions[Date] ) = 2021 )

Transactions Not in 2021 =
CALCULATE ( COUNTROWS ( transactions ), YEAR ( transactions[Date] ) <> 2021 )

Then a new Table visual, with CustomerID in the Values field and, in the Filters pane, set two filters for this visual, one for Transactions 2021 is equal to 0 and the other for Transactions Not in 2021 greater than 0.

Regards