Does anyone know how to dynamically filter time and date at the same time?
I have a DPU and FPY bar chart for our PDI area and I want to create a bookmark button that will automatically always show the results from Friday 9.30am to next Friday 9.29am, so realistically speaking if it’s Friday 9.28am there should be a full week of data and if it’s Friday 9.31am there should be usually nothing.
Do you have any ideas or videos on how this could be achieved?
Details:
DPU stands for Defects Per Unit and FPY is First Pass Yield, which are common quality metrics in Manufacturing industry.
I have three tables;
Calendar Table
Defects table - every time when defect is found, one row is created with the date, time and serial number of the vehicle (plus many other details but that’s not important here)
Vehicles Audited table - every time when a vehicle is audited, one row is created (date, time and serial number)
I’ve merged these two tables (all matching from Vehicles, I didn’t lose any records), Serial Number was my key.
I want to base the one week filter (from Friday 9.30am to Friday 9.29am) on the time and date when the vehicle was audited.
For this type of requirement I use offsets in my Calendar table. Now to be honest I never had to go beyond a weekly granulairity but I’m sure the same principles apply.
The idea is as follows, all “current” periods so weeks, months, quarters and/or FY are assigned the value 0, in your case this would be a DayTime frame. All periods in the past are negative and counting down and all periods in the future are positive counting up. Now here’s the key part, each time the datamodel gets refreshed it evaluates the M code for these offsets and that will allow you to move through time in your report…
Wether you create a full offset series OR just the one for the actual time frame you require is at your discretion. But once you have that column inside your datamodel you can use it in the Filter pane and/or your DAX statements something like: Calendar[myWeekTimeOffset] =0
A couple of assumptions:
Your current Calendar table is at a granulairity of minutes.
Your datamodel refresh is sheduld more frequent than ones a day
Remember. There is no internal clock in you report so for this to work the datamodel has to be refreshed.
Hi @Julian, we’ve noticed that no response has been received from you since the 11th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!