Get/count id of employee promoted in a timeframe

[ikelaiah]

Hi,

I have the following data.

+-----+----------------+--------------+----------------+-----------------+
| id  | contract_start | contract_end | contract_level | contract_reason |
+-----+----------------+--------------+----------------+-----------------+
| 111 | 2020-10-01     | 2020-11-01   |              2 | start           |
| 112 |  2020-10-15    | 2020-11-15   |              1 | start           |
| 111 | 2020-12-01     | 2021-01-01   |              2 | renew           |
| 112 |  2020-12-15    | 2021-01-15   |              1 | renew           |
| 111 | 2021-02-01     | 2021-03-01   |              2 | renew           |
| 112 |  2021-02-15    | 2021-03-15   |              1 | renew           |
| 111 | 2021-04-01     | 2021-05-01   |              3 | renew           |
| 112 |  2021-04-15    | 2021-05-15   |              1 | renew           |
| 111 | 2021-06-01     | 2021-07-01   |              3 | renew           |
| 112 |  2021-06-15    | 2021-07-15   |              2 | renew           |
+-----+----------------+--------------+----------------+-----------------+

What would be the best way to get/count id of employee promoted in 2021-2021 as indicated in the contract_level field?

Shall I approach this with Power Query or DAX?

Thank you.

[ryan_mayu]

@ikelaiah 

You can try this

Promoted = 
VAR _start=MINX(FILTER('Table',YEAR('Table'[Contract_start])=2021),'Table'[Contract_start])
VAR _startlevel=maxx(FILTER('Table','Table'[Contract_start]=_start),'Table'[Contract_level])
VAR _end=MAXX(FILTER('Table',YEAR('Table'[Contract_start])=2021),'Table'[Contract_start])
VAR _endlevel=maxx(FILTER('Table','Table'[Contract_start]=_end),'Table'[Contract_level])
return if(_endlevel<>_startlevel,"Yes","No")


_COUNT = COUNTAX(VALUES('Table'[ID]),[Promoted]="Yes")

pls see the attachment below

[ikelaiah]

@ryan_mayu , thanks for showing me this approach of using MINX and MAXX.

[aj1973 · Accepted solution]

Hi @ikelaiah 

here is the DAX formula :

[ikelaiah]

@aj1973 , thanks for this approach. I didn't think I could use DICTINCTCOUNT on it. So it gets me the count. Many thanks.

[aj1973]

Many weolcomes to @ikelaiah 

Please mark this thread as accepted for the rest of the community.