Track status change over time between dates

[Anonymous]

Hi all,

I'm trying to calculate the status of certain records on a day-by-day basis between 2 dates (Opened and Closed). I also have a 3rd date column which helps us determining the actual status of a record. Here is the logic I'd like to apply:
- if close date > Expeceted close date --> the status should be Delayed

- if close date <= Expeceted close date --> the status should be On Time

I'd like to end up with a graph (e.g.: linechart) where the X axis is Date and on the Y axis we can see the the sum of On Time and Delayed records separately over time. I recon I could create a new table with each day in it for each ID and calculate the actual status of that day on said ID, but I was looking for another solution, like 2 measures similar to this:

Number_of_OnTime =

CALCULATE(

COUNT('Table'[ID]),

'Table'[ExpectedCloseDate] >= Date_on_X-Axis)

Here is the link of the example file.

Thanks in advance!

[Anonymous]

Hi @amitchandak,

thanks, that's what I'd been looking for, very smart!

[amitchandak · Accepted solution]

@Anonymous , You can use the Active employee method of HR to plot open. And then you can check for date for delayed

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Yet to check the file