Help with historical data

[Clement_lav]

Hello,

I am currently creating a report and I would like a helping hand on the following problem:

The data is historized with a start and end date of the line (+ a last active line indicator)
We can therefore have for a functional key several lines but with only one active line (each line has a unique technical key).

My data model is as follows:
- 1 calendar table for the day
- 1 fact table for the day with for each date the technical key of the active line on this date
- X dimension tables linked to the fact table via the technical keys

So here is my problem:
When I make a table with for example all my quotes.
If a quote has several lines, they will all appear in the table.
I would therefore like to create a filter (ideally on the report or on the visual) allowing me to have only the active lines in relation to the selected date (by default that of the day).
Like this depending on the date, users will be able to go back in time and see the different changes that have been made.

Thanks in advance for your help and do not hesitate if I have not been understandable ...

[Anonymous]

Hi  @Clement_lav ,

Here are the steps you can follow：

You might consider appending dimension tables to a table using Power Query.

Connecting fact table to Append1 table.

This allows you to use the [date] of the fact table as a slicer to dynamically filter visual.

From your description I see that you want to set the slicer as default, as far as I know the power bi built-in slicer is not supported at the moment, you can consider using a custom visual -- “Preselected Slicer”.

We can get the three point in the "Visualizations", and we click the "Get more visual":

Then we find the "Preselected Slicer" and add the visual to our Power Bi Desktop.

First , we need to create a table with one column , and the value is True and False, like this:

Second , we can create a measure to define which the date we need to default preselected. If we need to default preselect then we return True else return False.

Flag =
IF(
    MAX('fact table'[Date])= TODAY(),TRUE(),FALSE())

Then we can put the [Date] in the Fields , [Flag] in the Pre Selection , 'Dirty Table'[Dirty Status] in the Dirty Status, like below screenshot: 

We can then dynamically pre-select today by default in the slice visualization, and when we select another date, we can restore the default selection by simply clicking the Reset button in the upper right corner.

Best Regards,

Liu Yang

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

[Clement_lav]

Thank you for your answer that I have just tested.

However, I still have some problems:
- The keys of my tables do not have the same name and are all linked to different tables (see simplified diagram)
- The validity indicator of the lines must be calculated by making selected date between start date of line and end date of line
- the filters do not work with my "sub dimensions" (e.g. Devis-Lignes)

Thank you in advance for your help