Group Time like on excel

Hi natabird3 

Assume you have a table like

1. open edit queries,

add column-> add date->date only,

add column-> add time->time only,

Close&&Apply

2. create calculated columns

hour = HOUR([Time])

time group =
SWITCH (
    TRUE (),
    [hour] >= 6
        && [hour] < 8, "6-8 am",
    [hour] >= 8
        && [hour] < 10, "8-10 am",
    [hour] >= 10
        && [hour] < 12, "10-12 am",
    [hour] >= 12
        && [hour] < 14, "12-14 pm",
    [hour] >= 14
        && [hour] < 16, "14-16 pm",
    [hour] >= 16
        && [hour] < 18, "16-18 pm",
    [hour] >= 18
        && [hour] < 20, "18-20 pm",
    [hour] >= 20
        && [hour] < 22, "20-22 pm",
    [hour] >= 22
        && [hour] < 24, "22-24 pm"
)

year/month/week/weekday =
YEAR ( Sheet6[only date] ) & "/"
    & MONTH ( Sheet6[only date] ) & "/"
    & WEEKNUM ( Sheet6[only date] ) & "/"
    & WEEKDAY ( Sheet6[only date], 2 )

3. create measures

max data = CALCULATE(MAX(Sheet6[data]),FILTER(ALLSELECTED(Sheet6),Sheet6[year/month/week/weekday]=MAX(Sheet6[year/month/week/weekday])))

color flag = IF(SUM(Sheet6[data])=[max data],1,0)

4. create a calendar table, create a relationship based on "Calendar"[Date] and "Sheet6"[only date].

calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "week", WEEKNUM ( [Date], 2 ),
    "weekday", WEEKDAY ( [Date], 2 ),
    "weekdayname", FORMAT ( [Date], "dddd" )
)

5. Add [weekdayname] in the row of the matrix,

Use [color flag] when configuring conditional formatting for the [Data] column.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.