Measure to calculate earliest change date per value

mll92 — Tue, 31 Jan 2023 15:46:04 GMT

I am creating a dashboard on a Dataverse table, and therefore cannot make many transformations. I also need the table to be live with automatic refreshes, so I cannot use additional tables (imported or virtual).

What I need to do is create a measure to show the day, hour, & time difference between the last refresh time and the last time a value changed. For example, in the table below, I need the last update time to show 1/31/2022 at 9:00 a.m., because that is the last time the "value" changed, and I need to calculate the time difference between 1/31/2022 at 9:00 a.m. and the last refresh time. I also have various "programs", so I need the calculation to account for the last time the value for that specific program was changed.

I currently have measures to show the difference between the last update (which in this case would be 1/31/2023 at 12:00 p.m. and the last refresh time, but now I need to calculate based on the time at the last value change. However, my other measure for "Notes" still needs to return the most recent note (!!!). Any suggestions?

Program	Value	Date/Time	Notes
Microsoft	4	1/30/2023 8:05 a.m.	🙂
Microsoft	5	1/31/2023 9:00 a.m.	😞
Microsoft	5	1/31/2023 12:00 p.m.	!!!

topic Measure to calculate earliest change date per value in DAX Commands and Tips

Measure to calculate earliest change date per value