topic Re: Dax calculation to identify lost business in the last 12 months within a 24 month period in DAX Commands and Tips

Dax calculation to identify lost business in the last 12 months within a 24 month period

KristianHamlin — Thu, 20 Apr 2023 14:24:17 GMT

I have a data set which covers the last 24 trading months at job level for each of our customers.

I want to establish out of all these accounts how we identify lost business across the last 12 months.

ie Customers were trading in the previous 12 months, now not traded in the last current 12 months.

Someone Please help

Re: Dax calculation to identify lost business in the last 12 months within a 24 month period

johnt75 — Thu, 20 Apr 2023 14:41:49 GMT

You could create a table like

Lost customers = VAR Last12Months = DATESBETWEEN ( 'Date'[Date], DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ), TODAY () ) VAR Prev12Months = DATEADD ( Last12Months, -1, YEAR ) VAR Last12MonthsCustomers = CALCULATETABLE ( DISTINCT ( Orders[Customer ID] ), Last12Months ) VAR Prev12MonthsCustomers = CALCULATETABLE ( DISTINCT ( Orders[Customer ID] ), Prev12Months ) VAR Result = EXCEPT ( Prev12MonthsCustomers, Last12MonthsCustomers ) RETURN Result

You should then be able to link that to your customer dimension.

Re: Dax calculation to identify lost business in the last 12 months within a 24 month period

FreemanZ — Thu, 20 Apr 2023 14:53:40 GMT

hi KristianHamlin

try to add a calculated table like:

Table = VAR List24M = CALCULATETABLE( VALUES(TableName[Customer]), TableName[DATE]>=EDATE(TODAY(), -24) ) VAR List12M = CALCULATETABLE( VALUES(TableName[Customer]), TableName[DATE]>=EDATE(TODAY(), -12) ) RETURN EXCEPT( List24M, List12M )

tried with a table like:

it worked like:

or you use the code as filter argument for CALCULATE for further aggregations.

Re: Dax calculation to identify lost business in the last 12 months within a 24 month period

FreemanZ — Thu, 20 Apr 2023 15:00:12 GMT

i.e. you may create measures like:

LostCount = VAR List24M = CALCULATETABLE( VALUES(TableName[Customer]), TableName[DATE]>=EDATE(TODAY(), -24) ) VAR List12M = CALCULATETABLE( VALUES(TableName[Customer]), TableName[DATE]>=EDATE(TODAY(), -12) ) VAR LostList = EXCEPT( List24M, List12M ) RETURN COUNTROWS(LostList)

LostList = VAR List24M = CALCULATETABLE( VALUES(TableName[Customer]), TableName[DATE]>=EDATE(TODAY(), -24) ) VAR List12M = CALCULATETABLE( VALUES(TableName[Customer]), TableName[DATE]>=EDATE(TODAY(), -12) ) VAR LostList = EXCEPT( List24M, List12M ) RETURN CONCATENATEX(LostList, TableName[Customer], ", ")

they worked like: