Counting a status every month (even after last modified)

[ciken]

Hi all, 

I am trying to build a table that will give me the status of a device in a given month. If a status does not change for multiple months, it should remain in that status. But what I end up with is just counting in the month of the change. 

Example: Device 123 is loaded and listed as "quarantine" in April 2023, it remains in quarantine until June 2023 when it becomes Saleable. In July it is sold and remains sold for all time after that. 

I have historical tracking so I can see when it makes a change to the status but I can't get it to count in a month where the status doesn't change!

My current formula is: 

Total Units =

VAR Currentdate = MAX('Calendar'[Dates])

VAR Active = CALCULATE((DISTINCTCOUNT('Equipment Inventory'[Id])),ALL('Calendar'[Dates]),'Equipment Inventory'[Date]<=Currentdate)

RETURN

Active

Currently, it looks like this...

	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23
Quarantined	1
Saleable			1
Sold				1

I'd like it to look like this...

	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23
Quarantined	1	1
Saleable			1
Sold				1	1	1

[amitchandak]

@ciken , Create a measure like

Calculate(lastnonblankvalue(Table[Date], max(Table[Status])), filter(all(Date), Date[Date]<= max(Date[Date]) ))

DAX functions: lastnonblankvalue , firstnonblankvalue: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=26940s

[ciken]

I'm still trying to figure this out. I added a DateDiff measure to the table to get the time an item remained in the status. Now i'm trying to figure out how to add that to my formula to count "date change was on xx month, keep in status for yy months". 

Any ideas?

[ciken]

This gave me a good start, but when I plugged that formula in, it gives me EVERY status through today, I only want it to fill in the status of a specific device if there is a blank between changes.  Here was the result your formula provided. 

The Count of ID is when the change took place, but then it pushes that status all the way to the end. 😞