topic Re: Date and Time Filters in DAX Commands and Tips

Date and Time Filters

amandabus21 — Fri, 30 Sep 2022 20:25:20 GMT

Hi,

Im having trouble filtering between a certain range of dates, and then after, filtering a certain range of hours.

I need all the incidents from 3/1/22 and 9/30/22. (That I can do with the sclicer)

Next, I need incidents only from 6:00 PM - 7:00AM between 3/1/22 - 9/30/22.

Thank you!

Re: Date and Time Filters

Greg_Deckler — Fri, 30 Sep 2022 21:15:02 GMT

amandabus21 Construct a TimeTable for your Time slicer:

HourTable = SELECTCOLUMNS( ADDCOLUMNS( GENERATESERIES(1,24,1), "Hour",TIME([Value],0,0) ), "Hour",[Hour] )

Then create a Complex Selector like:

Time Selector Measure = VAR __Opened = MAX('Table'[Date Opened]) VAR __MaxTime = MAX('TimeTable'[Hour]) VAR __MinTime = MIN('TimeTable'[Hour]) VAR __Time = __Opened - TRUNC(__Opened) RETURN SWITCH(TRUE(), __Time >= __MaxTime, 1 __Time <= __MinTime && __Time >= 0, 1, 0 )

Re: Date and Time Filters

amandabus21 — Fri, 30 Sep 2022 22:03:37 GMT

Greg_Deckler do i make the TimeTable as a column or a measure?

Re: Date and Time Filters

Greg_Deckler — Sat, 01 Oct 2022 12:35:29 GMT

amandabus21 As a table. In Report view in the Desktop, choose the Modeling tab and then New table and paste in that formula.

Re: Date and Time Filters

amandabus21 — Mon, 03 Oct 2022 15:39:23 GMT

Hi Greg, thanks for your help. Greg_Deckler

I was able to create the table but now when I create the measure I am getting a syntax error.

Re: Date and Time Filters

Greg_Deckler — Mon, 03 Oct 2022 16:01:00 GMT

amandabus21 Whoops, missed a comma after the first SWITCH condition:

Re: Date and Time Filters

amandabus21 — Mon, 03 Oct 2022 16:05:18 GMT

Greg_Deckler thank you.

Now how can I use that measure to filter the time between 6PM - 7AM?

Should I make a new column or is it supposed to be used a slicer?

Re: Date and Time Filters

Greg_Deckler — Mon, 03 Oct 2022 16:16:43 GMT

amandabus21 Meant to be used in the Filters pane actually. The intent was to slice using your TimeTable. However, you may need two time tables, one to set a min and one to set a max. And, if you need to go from one day to the next (PM -> AM) then you will probably need to tweak the measure selector accordingly.