PowerBI - Timeseries of categorical changes

[CliffordAP]

Hello all,

I have a simplified PowerBI data model here:  https://drive.google.com/file/d/13aXUR8mw2MdP3tw9K9LHxhqRCZqPaLwz/view?usp=share_link

I want to make a timeseries visual that accounts for when a user's status category changes over time.

This is what the model looks like: 

The current status table shows the status of users TODAY, and there is a separate table tracking the historical changes to their status. The visual so far looks like this: 

I need a measure of some kind that will evaluate the correct count of users' status over time. Something like: 

I have tried using LOOKUPVALUE, and different forms of CALCULATE but I haven't gotten very far.

Any help is greatly appreciated! Thank you

[brtfrschnr]

Test this out with your dataset...

This query finds the number of Actives, Frozen, Closed on a given day. I haven't tested if it works when users change from Frozen/Closed back to Active. Here is an output line chart:

let
Source = ***ENTER SOURCE HERE***,

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User ID", type text}, {"Status", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"User ID"]), "User ID", "Status"),
#"Filled Down" = Table.FillDown(#"Pivoted Column",{"1", "2", "3"}),
#"Merged Queries" = Table.NestedJoin(#"Filled Down", {"Date"}, Date, {"Date"}, "Query1", JoinKind.RightOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Date"}, {"Query1.Date"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Query1", each Date.IsInPreviousNMonths([Query1.Date], 2)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Query1.Date", "1", "2", "3"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Query1.Date", "Date"}}),
#"Filled Down2" = Table.FillDown(#"Renamed Columns",{"1", "2", "3"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down2", {"Date"}, "Attribute", "Value"),
#"Pivoted Column1" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
#"Sorted Rows" = Table.Sort(#"Pivoted Column1",{{"Date", Order.Ascending}})
in
#"Sorted Rows"

[CliffordAP]

Hello brtfrschnr,
Thank you for your response. I submitted this as a simplified data model with hopes that I can find a methodology with a DAX abstraction. It isn't as feasible for me to restructure the "actual" model in PowerQuery.