Dynamically querying/filling a timeframe using DAX based on table of timestamps

[crispybc]

I have a table of  ID, Status and Timestamps. 

ID	STATUS	TIMESTAMP
1	new	1/08/2022 0:33
1	open	1/08/2022 3:05
1	hold	4/08/2022 5:28
1	pending	12/08/2022 2:06
1	open	14/08/2022 23:48
1	solved	15/08/2022 5:57
2	new	1/08/2022 3:12
2	hold	1/08/2022 3:40
2	open	1/08/2022 4:10
2	hold	1/08/2022 4:30
2	open	2/08/2022 1:27
2	pending	5/08/2022 0:13
2	solved	8/08/2022 1:12
3	new	1/08/2022 4:16
3	open	1/08/2022 10:28
3	solved	2/08/2022 8:31

I would like to be able to dynamically select a date (based of a seperate related date table) and have it use the previous STATUS value (max TIMESTAMP<= DATE) of each ID. The final goal would be to summarize the number of IDs in each STATUS at a given point of time and to visualize this over time. (over longer time frames with resolution of days or weeks and also for smaller time frames eg. a single week at an hourly resolution)

I would need to use DAX to do this dynamically as filling values in sql or mquery would easily turn into billions of rows due to the number of ids and the resolution required.

So far everything I have tried in DAX has failed, so looking for some fresh takes as I am relatively new to DAX.

[v-yadongf-msft · Accepted solution]

Hi @crispybc,

If you want to get the previous STATUS value, as @amitchandak  said, please try following DAX:

Last Status = 
var _max = MAXX(FILTER(ALLSELECTED('table'),'table'[ID]=MAX('table'[ID])),'table'[TIMESTAMP])
return 
CALCULATE(max('table'[STATUS]), filter(('table'), 'table'[TIMESTAMP] =_max))

If you want to dynamically select a date to summarize the number of IDs in each STATUS, please create a slicer based on related Date table:

The Date table I created:

If I misunderstand your demands, please provide some data screenshots (remember to hide sensitive information) to help us better solve your problem.

Best regards,

Yadong Fang

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

[amitchandak]

@crispybc , if this is a really big table, I would advise doing the transformation in datawarehouse, if that can be done there. getting the last status on a large table can be costly

I think you need something like this -https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0