Compare and visualise user status in time

[FinbarB]

Hi, 

I can't seem to find the proper measure to track changes in time for users in a table. I have a table with a weekly status per user, the status is text based ("active", "Hibernating, "new", "Regular", etc...). 

From this table I have created two calculated tables: 

- One table contains the latest status per customer ID (based om max date function)

- One table contains all previous statusses for this user (excluding the latest). 

- Both tables are have a generic date table with whom the have a bidirectional relation 

- Both tables have a bidirectional relation with a customer table (which contains all customer IDs) 

I now have slicers that can select and count users by their latest status (a distinccount by current status).

Additionally I have a slicers based on the older status (to see what the status was before)

A third slicer tells me if they are "new" in their current status

What I am looking for is a way to see how they progressed in time to their current state, without counting them as duplicates in the past (for instance 90% of customers have the same state over a longer periode, I would like to be able to filter those out using a separate slicer).  

I have not been able to filter these out (on every run date the distinct count causes the customer IDs to be counted, causing unreadable visuals). 

In the screenshot you can see how I would like to build up the report and the slicers as part of that (distinct counts across time, and wether a customer is new to the segment (plus the option to de-duplicate). The counts in the visual are based on a weekly total count (regardless of status havin changed)

Hope someone can help me solve this?

Greatly appreciated, 

Finbar

 @amitchandak Some of your replies to posts seem similar to this challenge, but I haven't been able to match thos with this particular challenge....

[v-henryk-mstf]

Hi @FinbarB ,

In accordance with the requirements in the link below, further provide the necessary demand information and test models(delete sensitive information), so that I can answer for you.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Henry

[FinbarB]

Hi @v-henryk-mstf 

Please find example data and PBI report based on this data: 

https://1drv.ms/u/s!AoOmnGFjz-d2hP5WgSCL1fYSFEZwpg?e=GdvoQt