Forum Discussion

Steve_AAA's avatar
Steve_AAA
Frequent Visitor
3 years ago

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...
Need Help
v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
Steve_AAA
Frequent Visitor
3 years ago

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?