Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Steve_AAA
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:

 

Steve_AAA_0-1682096925458.png

 

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Steve_AAA 

 

You can try the following methods.

Column:

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

vzhangti_0-1682318734449.png

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])))

vzhangti_1-1682318803522.png

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?

 

Steve_AAA_0-1682321916914.png

Steve_AAA_1-1682322010650.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors