LookupValue in one table based on time period

[Anonymous]

Hi everyone,

I am trying to find a meassure for this example. I have one table of 2 years period (2021-2022), which includes client IDs and they can repeat. I need to calculate distinctcount of client IDs, which exists in 2021, but not exist in 2022. 

In this example it should be distinctcount of klient_id = 2 (K100001 and K100003), because these are in 2021, but not in 2022. Also, I would like to use the meassure in bigger dataset then, so the meassure should comparing always last 12 months of date and previous 12 months from it (2021 and 2022; 2020 and 2021; 2019 and 2020; etc.).

Thanks for help. 

[tamerj1]

Hi @Anonymous 
Do do you want to compare current year with the previous year or compare the past 12 months (from the max date available in the current filter context) to the 12 months before? Do you have adate table? 

[Anonymous]

Hi, 

I want to compare the past 12 months to the 12 months before.

[johnt75 · Accepted solution]

You can try 

Lost clients =
VAR MaxYear =
    CALCULATE ( YEAR ( MAX ( 'Table'[Date] ) ), REMOVEFILTERS () )
VAR PrevYear = MaxYear - 1
VAR CurrentYearClients =
    CALCULATETABLE (
        VALUES ( 'Table'[Client ID] ),
        TREATAS ( { MaxYear }, 'Date'[Year] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR PrevYearClients =
    CALCULATETABLE (
        VALUES ( 'Table'[Client ID] ),
        TREATAS ( { PrevYear }, 'Date'[Year] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR NumLostClients =
    COUNTROWS ( EXCEPT ( PrevYearClients, CurrentYearClients ) )
RETURN
    NumLostClients

[Anonymous]

This works, thank you 🙂