How to get the latest event before a specific date? Count with filtering

[Anonymous]

Hi,

I have data as below. What I am trying to achievie is to calculate headcount on a particular positions, using a chosen date (independent from the table).

Dep	Worker	Event	Position	Event_order	Date
IT	Adam	Hire	Junior	1	01/01/2020
IT	Adam	Promotion	Senior	2	01/06/2021
IT	Adam	Promotion	Expert	3	01/09/2022
IT	Adam	Promotion	Manager 1	4	01/10/2023
HR	Ben	Hire	Expert	1	01/03/2020
HR	Ben	Promotion	Manager 1	2	01/08/2022
HR	Ben	Promotion	Manager 2	3	01/12/2023

Assuming I set the date filter to 31-08-2022 I would like to get the results as following:

(pivot table, where Rows = Department, Columns = Position, value = count of employees on a particular position at the selected date)

Dep	Junior	Senior	Expert	Manager 1	Manager 2
IT		1
HR				1

I tried to do COUNT with FILTER on date, but I couldn't manage to get only the latest position. 

calculate(count(table[Worker]), FILTER(table, table[Date]<=_min))

The calculation above returns 1 also for each previous positions (so for Adam: Junior and Senior, for Ben Senior, Expert and Manager).

any ideas?

[Anonymous]

Hi @Anonymous ,

Please try like:

Measure = 
VAR _max_date =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED () )
VAR _max_order =
    CALCULATE ( MAX ( 'Table'[Event_order] ), ALL ( 'Table'[Position] ) )
VAR _position =
    CALCULATE ( MAX ( 'Table'[Position] ), 'Table'[Event_order] = _max_order )
VAR _result = SUMX('Table',IF('Table'[Position]=_position && 'Table'[Event_order] =_max_order,1))
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Hi, @Anonymous . It doesn't work on my side.

Still getting "1" at the top event (regardless the date).

Edit: I think I know what you did, but the date filter can't be filtering my data. It should be an independent date to choose.

[VahidDM]

Hey @Anonymous 

What expression have hou used for _min?

can you share the full codes,

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Anonymous]

hi @VahidDM 

var _min = minx(allselected(GenTableDates),GenTableDates[GDate])

it comes from a table with generated dates, but it doesn't really matter