Forum Discussion
Historization valid from valid to
Good morning,
Does anyone have an idea how I can display only those records that are within my validity period?
I would like to be able to look back in my historical data and see the records that are only valid at that time.
My data set always has a valid from and a valid to timestamp. The most current data set is set to Valid to 31.12.9999.
One possibility is probably a calculated table. But this is also a very slow method.
6 Replies
- az38
Community Champion
how to you want to display it and why do you think calculated table is so slow?
In the most common case you can create a Calendar Table with additional column like
Calendar Table = ADDCOLUMNS( CALENDARAUTO(), "Total Value", CALCULATE(SUM(Table[Value]), FILTER(Table, Table[StartDate] <= EARLIER([Date]) && (Table[EndDate] >= EARLIER([Date]) || ISBLANK(Table[EndDate]) ) ) ) - amitchandak
Super User
dsi , Not clear. Can you share sample data and sample output in table format? see if first/last nonblank value can help
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak
The filter should apply to the complete report as it determines the validity of all data and calculations.
So not only in a map / table ...
It is best for the user to select a key date (dropdown). If the key date lies between valid from and valid to, it is valid and should be displayed.
- v-easonf-msft
Community Support
Hi, dsi
It sounds like you need to create a calendar date table:
Ex:Calendar = CALENDAR(DATE(2019,01,01),DATE(2021,01,01))You can define the time range of the calendar table yourself, and then establish a one-to-many relationship between the date field in the fact table and the date field of the date table.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.No, that doesn't solve my problem. If I select 15.07.20 in the calendar, I only want to see records that are valid from before and valid to after.
