topic Re: LookupValue in one table based on time period in DAX Commands and Tips

LookupValue in one table based on time period

Anonymous — Thu, 15 Jun 2023 10:05:47 GMT

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.

Re: LookupValue in one table based on time period

johnt75 — Thu, 15 Jun 2023 10:59:00 GMT

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

Re: LookupValue in one table based on time period

tamerj1 — Thu, 15 Jun 2023 11:03:38 GMT

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?

Re: LookupValue in one table based on time period

Anonymous — Thu, 15 Jun 2023 11:35:14 GMT

Hi,

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

Re: LookupValue in one table based on time period

Anonymous — Thu, 15 Jun 2023 11:50:39 GMT

This works, thank you 🙂