A Measure That Tracks changes in data over a year

[Toora]

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

[timalbers · Accepted solution]

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

[Anonymous · Accepted solution]

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

[timalbers · Accepted solution]

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