Forum Discussion

ElliotP's avatar
ElliotP
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

How to Create DayName + TimePeriod

Evening,   I have my data table, my date table and my time table. I'm trying to be able to calculate "Monday Morning". At the moment, I'm able to calculate the Day name from my Date Key using forma...
Need Help
Show and Tell
Tutorial Requests
  • Sean's avatar
    Sean
    9 years ago

    ElliotP

     

    It sounds like you are going with Phil_Seamark's formula so just use a SWITCH to do all your nested IFs like this...

     

    Day and Time =
FORMAT ( 'itemdetailsdogfood$'[Date], "dddd " )
    & SWITCH (
        TRUE (),
        'itemdetailsdogfood$'[Time] >= TIMEVALUE ( "00:00:00" )
            && 'itemdetailsdogfood$'[Time] <= TIMEVALUE ( "06:00:00" ), "Night",
        'itemdetailsdogfood$'[Time] > TIMEVALUE ( "06:00:00" )
            && 'itemdetailsdogfood$'[Time] <= TIMEVALUE ( "09:00:00" ), "Early Morning",
        'itemdetailsdogfood$'[Time] > TIMEVALUE ( "09:00:00" )
            && 'itemdetailsdogfood$'[Time] <= TIMEVALUE ( "12:00:00" ), "Morning",
        'itemdetailsdogfood$'[Time] > TIMEVALUE ( "12:00:00" )
            && 'itemdetailsdogfood$'[Time] <= TIMEVALUE ( "18:00:00" ), "Afternoon",
        'itemdetailsdogfood$'[Time] > TIMEVALUE ( "18:00:00" )
            && 'itemdetailsdogfood$'[Time] <= TIMEVALUE ( "21:00:00" ), "Evening",
        "Late Evening"
    )

    Good Luck! :smileyhappy:

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Or you can just add the following calculated column to your [itemdetailsdogfood$] table.

 

Day and Time = FORMAT('itemdetailsdogfood$'[Date],"dddd ") &
if(
'itemdetailsdogfood$'[Time] < TIMEVALUE("12:00:00") ,
"Morning" ,
"Afternoon"
)