topic Re: Time Travel Reporting? in DAX Commands and Tips

Time Travel Reporting?

debenaire — Mon, 27 Nov 2023 15:48:32 GMT

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!

Re: Time Travel Reporting?

Anonymous — Wed, 29 Nov 2023 01:18:38 GMT

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.

Re: Time Travel Reporting?

debenaire — Wed, 29 Nov 2023 09:37:10 GMT

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.

Re: Time Travel Reporting?

Anonymous — Thu, 30 Nov 2023 03:01:20 GMT

Hi debenaire ,

Below is my table:

It doesn't need DAX to implement your result plot, just slicer filtering.Maybe I misunderstood the whole process

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.

Re: Time Travel Reporting?

debenaire — Thu, 30 Nov 2023 08:34:56 GMT

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

Re: Time Travel Reporting?

Anonymous — Mon, 04 Dec 2023 06:13:13 GMT

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.