Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.