Forum Discussion
Filtering duration in a table with multiple date selection
Working with durations can be tricky. What I often do (as I learned a few months ago on this community) is to create a table that contains all possible rows. First step is to add an index column to the orignal table, then create a calculated table like this:
Ind ID Values Start Date INDEX 1 10 10/01/2019 8:02:00AM 1 1 10 10/01/2019 8:03:00AM 1 1 10 10/01/2019 8:04:00AM 1 etc right upto 10/01/2019 5:00:00PM. This does result in a lot of rows however.
Next is to create a 1-many relationsship from orignalTable[INDEX] to newTable[INDEX] and the Stat Date column and your datetable.
Then you create a slicer with you date table and add the columns from the orignalTable to a visual (matrix or table for example). These then should be filtered 🙂
To generate the table above, have a look at this post here:
Re: How to calculate time between two changing status
Let me know if this works!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Working with durations can be tricky. What I often do (as I learned a few months ago on this community) is to create a table that contains all possible rows. First step is to add an index column to the orignal table, then create a calculated table like this:
| Ind ID | Values | Start Date | INDEX |
| 1 | 10 | 10/01/2019 8:02:00AM | 1 |
| 1 | 10 | 10/01/2019 8:03:00AM | 1 |
| 1 | 10 | 10/01/2019 8:04:00AM | 1 |
etc right upto 10/01/2019 5:00:00PM. This does result in a lot of rows however.
Next is to create a 1-many relationsship from orignalTable[INDEX] to newTable[INDEX] and the Stat Date column and your datetable.
Then you create a slicer with you date table and add the columns from the orignalTable to a visual (matrix or table for example). These then should be filtered 🙂
To generate the table above, have a look at this post here:
Re: How to calculate time between two changing status
Let me know if this works!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Hi JarroVGIT
Thanks for this clean solution. I think for this requirement one calculated table is required.
Just want to confirm one thing:
The code for Genrate(Table, Generateseries(_StartDate,_EndDate,Time(0,1,0))) will be breaking the main table rows from start time to end time as per time interval row by row right?
Mann