Return last Value by Date and grouped by ID

[Chuck1892]

Hello folks,

I have a problem that has been bothering me for a few days now. I have a log table that shows the state change of a position (ID).
I would now like to use the log to determine which positions were set to "Active" in each fiscal week.
The "result" tables needs the explicit IDs for further calculations, but for now I want to calculate the number of positions per fiscal week.

Example Log:

My first theory thought was to filter the log by fiscal week and then summarize it by position_id and get the Latest "changedTo" for each position_id. Then i would filter the summarized table and volià ... All active positions.

But annoyingly, the technical DAX implementation is lacking on my part.

As far as i understand, it is not possible to reference a column of a "variable table". So something like this would not work .. (hardcored _curKW for testing)

VAR _curKW = 202421
VAR _filteredStateChange =
CALCULATETABLE(
'EmploymentPosition StateChange',
'EmploymentPosition StateChange'[ISO YearFW] < _curKW
)
VAR _lastPosState_inKW =
SUMMARIZE(
_filteredStateChange,
'EmploymentPosition StateChange'[employmentPosition_id],
"LastChange",
MAX(_filteredStateChange[changedTo])
)

Unfortunately, I can't think of any other way - would anyone here have any ideas to steer me in the right direction?

I would be grateful for any help!

Regards!

[some_bih]

Hi @Chuck1892 so basically, your expected results should be which employee was active during each fiscal weeks?

If yes, one approach is to 

ADDCOLUMNS (
SUMMARIZE (YourCentralTableName, FiscalweekID, EmployeeID), "Number of distinct stautus", CALCULATE (DISTINCTCOUNT(YourTableName), column changed to="Active"))

After that filter this table using variable to status Active or what is your definition for Active, as you have blank in column changed to.

The best would be to share file and provide expected examples.

[some_bih]

Hi @Chuck1892 

It could be that your analysis need only one set of data: <YourTableName>[changedTo] column value is "Active"?

If yes then one of possible option is to use Power Query step to filter only that column and this status.

[Chuck1892]

Hey! Thanks for your reply.
Unfortunately, that wouldn't work. For the final visualisation, the values should be displayed depending on the calendar week.
For example, if position 1234 is set to active in calendar week 1, inactive in calendar week 3 and active again in calendar week 4 and position 9876 is set so active in week 2 and inactive in week 4 then the calculated table (simplified) should look like this (The variable _curKW would normally be SELECTEDVALUE(Calendar[YearFiscalWeek] )

For YearFiscalWeek = 202402

employmentPosition_id	LastChange
1234	Active
9876	Active

So there are two active positions in Week 02

For YearFiscalWeek = 202403

employmentPosition_id	LastChange
1234	Inactive
9876	Active

Active Positions in Week 03 = 1

I hope my explanations are reasonably understandable. 😅


//Edit: Found an example in the logfile!

If I filter the log to active only, both employmentPositions would be counted both in FiscalWeek 19 (Pink - which would be correct) and in FiscalWeek 20 (yellow), which would be wrong, as they have been set to inactive.