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?
|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.||!!!|