Measures based on points in time

[Steve_AAA]

I have a location status table in MS SQL Server. This table consists of a location code, a status and the date the location was changed to this status. I would like to create a measure in Power BI to count the different statuses depending on a point in time. For example at the end of January 90 locations were in an 'Open' status because there are records in the database for those locations opened at some point in January. At the end of February though, 10 of those locations were closed so the measure for Feb should be 'Open' = 80 and 'Closed' = 10. There are many more statuses in our list though, so I don't want to hard code the statuses themselves into the measure if I can help it. 

I don't know if I need to go back to the source and extrapolate the data first in SSMS then bring that extrapolated data into Power BI. This way I could link to a date table and perform counts based on individual dates. I have a lot of locations though, so the extrapolated data could be very very large, see very simplified example below:

[v-zhangti]

Hi, @Steve_AAA 

You can try the following methods.

Column:

LastDate = MINX(FILTER(ALL('Table'),[Date]>EARLIER('Table'[Date])),[Date])

New table:

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Measure:

New Location = CALCULATE(MAX('Table'[Location]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Date'[Date])&&[LastDate]>SELECTEDVALUE('Date'[Date])))

New Status = CALCULATE(MAX('Table'[Status]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Date'[Date])&&[LastDate]>SELECTEDVALUE('Date'[Date])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Steve_AAA]

Thank you for the reply. This is almost perfect, but in this example it doesn't track that location 'L1' is in a status of 'Decomissioned'. I think this is because in this example there is only one line for 'L1' at this status. If I add a second date for 'L1' at 'Decommissioned' status then it does appear in the table. Is there anything I can do to ensure that all data is shown in the table?