Time Travel Reporting?

debenaire · ‎11-27-2023

Hi, I am storing data which timestamps any changes to records and inserts them into a table as a new record. As such there will be duplicate IDs but with slightly different data where there have been changes

I want to use this data for "time travel" purposes.

Ie. "On the 27th of February 2023, what was the list of sites.

My thinking is to create a calculated table using a parameter that is configured in a report to effectively filter the table based on the first value for each record prior to the date in the parameter.

My table has headings:

LoadUTC (the date the new record is loaded)

Id (the unique referecne - although there will be multipes of these as data is changed)

Any thoughts on this approach please and if so what would the dax look like? Also mindful at scale will this approach be an issue?Thanks in advance!

debenaire · ‎11-30-2023

thanks but what we need is the slicer to have every possible day even if that day is not the exact same create date, the logic will choose the first date equal to or less than the slicer date for each id

Anonymous · ‎12-03-2023

Hi @debenaire ,

You can create a New table:

Table = CALENDAR(DATE(2000,1,1),NOW())

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

Anonymous · ‎11-28-2023

Hi @debenaire ,

Can you give us a sample data table, so that we can help you solve the problem quickly, thank you.

Best Regards,

Xianda Tang

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

debenaire · ‎11-29-2023

Thank you. see attached example where we can see different entries for the same dataset id, based on the parameter / slicer selected in a visual it would filter the table to the results seen.