I want to get an indicator of 1 only when 'sector' changes for the same 'num_emp' from one month to other month and 0 otherwise, using DAX in PowerBI

Question

I have a table like this

num_emp	sector	month
1	111	4
2	222	4
1	111	5
2	222	5
1	222	6
2	222	6
1	222	7
2	222	7

I want to add a column where if the sector changes from one month to other, I get 1, otherwise I get 0. Something like this:

num_emp	sector	month	change
1	111	4	0
2	222	4	0
1	111	5	0
2	222	5	0
1	222	6	1
2	222	6	0
1	222	7	0
2	222	7	0

As you can see the employ in month 6 change from sector 111 to 222.

I'm really lost on who to do this

Answer 1

Try something similar to:

changePost = 
  var prevRows = FILTER('YourTable', [month] < EARLIER([month]) && [num_emp] = EARLIER([num_emp]))
  var lastRow = TOPN(1, prevRows, [month], DESC)
  var lastSector = CALCULATE(MIN('YourTable'[sector]), lastRow)
  return IF(COALESCE(lastSector, [sector]) <> [sector], 1, 0)


changePre = 
  var postRows = FILTER('YourTable', [month] > EARLIER([month]) && [num_emp] = EARLIER([num_emp]))
  var firstRow = TOPN(1, postRows, [month], ASC)
  var firstSector = CALCULATE(MIN('YourTable'[sector]), firstRow)
  return IF(COALESCE(firstSector, [sector]) <> [sector], 1, 0)