topic Re: A Measure That Tracks changes in data over a year in DAX Commands and Tips

A Measure That Tracks changes in data over a year

Toora — Mon, 26 Feb 2024 08:21:21 GMT

Hi, I'm trying to come up with an expression that captures when a customer changes branch. See table below which reports on when customers made a visit. Basically I need the measure will return "1" for customer Z2225 as they moved branch

Date Customer Code Branch_Code

01/01/2022	L55	LIM
02/01/2022	A11	BEL
03/01/2022	A55	BEL
04/01/2022	552135051	LIM
05/01/2022	Z2225	BEL
06/01/2023	Z2225	LIM
07/01/2023	s22	BEL
08/01/2023	d22	BEL

Re: A Measure That Tracks changes in data over a year

timalbers — Mon, 26 Feb 2024 09:22:18 GMT

Hi Toora

try something like this as a new calculated column:

branch_change = VAR prev_date = MINX( TOPN( 1, FILTER( your_table, your_table[Customer Code] = EARLIER ( your_table[Customer Code] ) && your_table[Date] < EARLIER( your_table[Date] ) ), your_table[Date], DESC ), your_table[Date] ) VAR prev_branch = LOOKUPVALUE( your_table[Branch_Code], your_table[Date], prev_date ) VAR change = SWITCH( TRUE(), prev_branch = BLANK(), BLANK(), prev_branch <> your_table[Branch_Code], 1 ) RETURN change

Remeber replacing "your_table" with your actual table name.

If this post helped you, please consider marking it as solution, thank you!

Cheers

Tim

Re: A Measure That Tracks changes in data over a year

Anonymous — Tue, 27 Feb 2024 05:29:34 GMT

Hi Toora ,

Here are the steps you can follow：

1. Create measure.

Measure = var _count=COUNTX(FILTER(ALL('Table'),'Table'[Customer Code]=MAX('Table'[Customer Code])),[Branch_Code]) var _maxdate=MAXX(FILTER(ALL('Table'),'Table'[Customer Code]=MAX('Table'[Customer Code])),[Date]) var _mindate=MAXX(FILTER(ALL('Table'),'Table'[Customer Code]=MAX('Table'[Customer Code])&&'Table'[Date]<_maxdate),[Date]) var _newbranch=MAXX(FILTER(ALL('Table'),'Table'[Customer Code]=MAX('Table'[Customer Code])&&'Table'[Date]=_maxdate),[Branch_Code]) var _oldbranch=MAXX(FILTER(ALL('Table'),'Table'[Customer Code]=MAX('Table'[Customer Code])&&'Table'[Date]=_mindate),[Branch_Code]) return IF( _count>1&& MAX('Table'[Date])=_maxdate&&_newbranch<>_oldbranch,1,0)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly