Create "current status" from series of rows time stamping when status changes

[mnt23]

Hi all, 

I have a scrubbed version of my data (unique ID, date and status) here. 

The data tracks membes of my team as the deploy to a task, are employed on, the task ends and they are then recovered back to their home office. 

This is stored as a series of rows - i.e. whenever an individual's status changes (and sometimes when it doesn't) a new row is created with a date and the status that they have changed to. This is all held together by a Unique ID - this ID is unique to an individual and the task, not to each row. 

I'd like to be able to plot the number of individuals for any given status, over time. I have a date dimension table already, I think all I need to do is work out how to calculate the measure to plot but I could be wrong. 

Any thoughts? I think it will look like some sort of if statement checking statuses and dates but cannot get my had around actually writing it. 

Matt

[Anonymous]

Hi  @mnt23 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
CALCULATE(
    DISTINCTCOUNT('True'[Unique ID]),
    FILTER(ALL('True'),
    'True'[Daily Update Date]=MAX('True'[Daily Update Date])&&
    'True'[Daily Update Status]=MAX('True'[Daily Update Status])))

2. Create calculated column.

Column =
var _index=
CALCULATE(
    MAX('True'[Daily Update Status]),FILTER(ALL('True'),
    'True'[Unique ID]=EARLIER('True'[Unique ID])&&'True'[Daily Update Date]=EARLIER('True'[Daily Update Date])))
var _index_1=
CALCULATE(
    MAX('True'[Daily Update Status]),FILTER(ALL('True'),
    'True'[Unique ID]=EARLIER('True'[Unique ID])&&'True'[Daily Update Date]=EARLIER('True'[Daily Update Date])-1))
    return
IF(
    'True'[Daily Update Date]=MINX(FILTER(ALL('True'),'True'[Unique ID]=EARLIER('True'[Unique ID])),[Daily Update Date]),'True'[Daily Update Status],
    IF(
    _index<>_index_1,'True'[Daily Update Date]&" "&_index_1&" -> "&_index,'True'[Daily Update Status]))

3. Create two pages.

Page1;

Page2:

4. Use the link below to turn Page2 into a Tooltip.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-tooltips?tabs=powerbi-desktop

5. Result:

When you move the mouse to a date in Page1, the Status conversion data of Page2 will be displayed:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mnt23]

Hi Lui,

Thanks for coming back to me. I'm struggling to understand what you've done, are you able to quickly explain it for me?

[amitchandak]

@mnt23 , refer if my blog on a similar topic can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

[mnt23]

Hi amitchandak, 

That does give me the latest status of each individual, but it doesn't enable me to plot that status over time - if I try and add it to a visualisation like a stacked bar chart against time, it simply doesn't let me add it to the values box.

What I'm aiming for is a chart that has date along the bottom, and can then plot the total number of individuals, by status, for each day. 

Unfortuntely i don't quite understand how your measure works, so I'm struggling to change it.