Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Measures based on points in time

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:




Community Support
Community Support

Hi, @Steve_AAA 


You can try the following methods.


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


New table:

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


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.

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?





Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors