Forum Discussion

ElliotP's avatar
ElliotP
Post 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:

DoubleJ's avatar
DoubleJ
Solution Supplier
9 years ago

Hi

 

I've downloaded your pbix file.  I guess

- "itemdetailsdogfood$" is the data table

- "ExtendedCalendar" is the date table

- "HourMinuteSecond" is the time table

right?

 

If you want to have the "Monday Morning" column in the data table, you could:

- Add a column in the time table

DayTime = if(HourMinuteSecond[Hour24] < 13,"Morning","Afternoon")

- You already have the DayName in the date table

- So you can simply concatenate these 2 columns in the data table with the RELATED function

DayPeriod = RELATED(ExtendedCalendar[DayName]) & " " & RELATED(HourMinuteSecond[DayTime])

Hope this helps

JJ

  • ElliotP's avatar
    ElliotP
    Post Prodigy
    9 years ago

    Thank you for the responses.

     

    Yes DoubleJ you are right about the tables.

     

    DoubleJPhil_SeamarkThat's 100% what I'm after. How would I be able to do the IF functions so I can have different time periods as opposed to a binary choice. Such as:

     

    IF 'itemdetails$dogfood'[time] >= TIMEVALUE("00:00:00") & TIMEVALUE("03:00:00"), Morning

     

    I have five or six catagories, Late Evening, Morning, Lunch, Afternoon, Dinner, Evening.

    • Sean's avatar
      Sean
      Community Champion
      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
        Microsoft Employee
        9 years ago

        I like the way the SWITCH statement has been used here to allow ranges.  No excuses for nested IF statement ever again!