Report on date/time period Friday 7pm to Friday 7pm

[ktbell]

Hi,

I need to create a weekly report on emails received over the previous week between 19:00 Friday and 18:59 the following Friday.

My visual needs to display the figures for the following:

   Friday (19:00-23:59)

   Saturday (00:00-23:59)

   Sunday (00:00-23:59)

   Monday (00:00-23:59)

   Tuesday (00:00-23:59)

   Wednesday (00:00-23:59)

   Thursday (00:00-23:59)

   Friday (00:00-18:59)

   Total received

Here is an example of the query data from the mailbox, with the rows highlighted red being the ones I need the filter to pick up:

I can't seem to find a way to use the slicer to filter on both date and time.

I wondered if I could create a date/time table (I know not recommended) that contained the hour of each day, starting at 19:00 Friday and finishing at 18:00 the following Friday, but unsure how to do this and if it would be too memory intensive.

Any suggestions on how else I can achieve this?

[dedelman_clng · Accepted solution]

Hi @ktbell -

Have you tried putting a calculated column on your data, putting each row into a "group" of sorts?

Something like this:

DisplayGroup =
VAR __myDOW =
    WEEKDAY ( FridayGroups[DT], 1 )
VAR __myTime =
    HOUR ( FridayGroups[DT] )
RETURN
    SWITCH (
        TRUE (),
        AND ( __myDOW = 6, __myTime >= 19 ), "Friday (19:00-23:59)",
        __myDOW = 6, "Friday (0:00-18:59)",
        CONCATENATE ( FORMAT ( FridayGroups[DT], "dddd" ), " (0:00-23:59)" )
    )

Additionally/alternatively, you create a date group dimension table with an ID and group name (1, "Friday (19:00-23:00)", etc), then use the calculated column to just return 1-8 and make a relationship between the group IDs. That would go something like this:

DisplayGroup =
VAR __myDOW =
    WEEKDAY ( FridayGroups[DT], 15 ) //15 has Friday = 1, Thurs = 7
VAR __myTime =
    HOUR ( FridayGroups[DT] )
RETURN
    SWITCH (
        TRUE (),
        AND ( __myDOW = 1, __myTime >= 19 ), 1,
        __myDOW = 1, 8,
        __myDOW
    )

Hope this helps

David