How to find changes in postion with dax

[TK12345]

Hi there,

I have an issue with the following. I need to know when the position of 1 employee has changed, based on the filter Month/Year. So when you see the picture, in 2019-06 till 2019-12 this employee has 1 function, but in 2021-02 he had another function so from that (month/year) the dif.functions column need to change to 2. So when I have a filter with (month/year) and I say 2019-8 it has to say 1 function. But changing the filter to 2021-03 it needs to say 2 because the filter changed.

[speedramps]

Please cosnider this solution. Click the thumbs up icon for trying to helping you and then click SOLVED if it fixes your problems. Cheers !

All positions =

VAR mymonthyear = SELECTEDVALUE('HR Data'[MonthYear]) -- get the current row date

RETURN

CALCULATE( -- use calculate to override the row filters

DISTINCTCOUNT('HR Data'[Position]), -- get the number of positions

REMOVEFILTERS('HR Data'[Position]), -- remove the current row position filters because you want to count all positions

'HR Data'[MonthYear] <= mymonthyear -- only get postions up to an including the current row month

)

[TK12345]

Hi there,
thanks for your help. Unfortunately it still does not work the way I would like to. When I make a matrix with the All positions filter it is still not 2. 

As you can see in the picture, I need to show 2 when it changed the position. 
And when I filter on 2021-02 it also needs to show 2, but it show 1 as well.

Hope you can help me, and thanks for your help last week.

[speedramps]

Hi again TK12345

A slicer does what it is says on the tin.  It ‘slices’ your input data.

So you can’t slice a table by year 2021-02 and still include the other months.

A simple work around is to have 2 tables. Your master table and a picklist table with year/months with no relationship.

Then create 2 measure:-

All positions =

VAR mymonthyear = SELECTEDVALUE('HR Data'[MonthYear]) -- get the current row date

RETURN

CALCULATE( -- use calculate to override the row filters

DISTINCTCOUNT('HR Data'[Position]), -- get the number of positions

ALLEXCEPT('HR Data','HR Data'[Employee]),  -- remove all filters expcet the employee

'HR Data'[MonthYear] <= mymonthyear -- only get postions up to an including the current row month

)

All postitions with slicer =

VAR mymonthyear = SELECTEDVALUE('HR Data'[MonthYear]) -- get the current row date

VAR pickedmonth = SELECTEDVALUE('Date picklist'[MonthYear]) -- get the slicer date

RETURN

-- only return a result if the picklist date = the row date

IF(mymonthyear = pickedmonth, [All positions])

Click here to download an example 

PREVIEW

Please remember we are a community and dont get paid to give help, so please click the thumbs up for me helping you and the solved button if this fixes your problem.

[v-kelly-msft]

Hi  @TK12345 ,

Create a column as below:

Column = RANKX(FILTER('Table','Table'[Employee]=EARLIER('Table'[Employee])),'Table'[Position],,ASC,Dense)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[TK12345]

Like this one, it needs to be different. 

 but we are almost there, thanks for the help so far

[v-kelly-msft · Accepted solution]

Hi  @TK12345 ,

Yes,using Power query can solve it and will be much easier.

First group rows as below:

Then add an index column from 1 and expand the date column;

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[TK12345]

Hi there,

Thanks for this one, wil almost give it "accept as solution".  De one in the screenshot is working great, but when i check it with other employee numbers it is exactly the opposite. As you can see in the picture underneeth. When i look into this one it says 1 by the second function, it has to be based on the Month/Year. So we are so close, but maybe this one is possible to solve with a extra filter or in the power query?