Forum Discussion
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 format in my date table.
I'm able to calculate the Time Period (Morning, Afternoon, etc) by using a conditional column and the 24Hour number and simply doing it that way (so 15=Afternoon, 8 = Morning, etc).
It would be amazing to be able to bring these two together but I'm a little out of ideas. They are on different tables which I'm not able to link with an active relationship due to them both have an active relationship with my date table. I've considered somehow producing either the Monday or the Morning aspect on the date table and then using the CONCENTRATE and RELATEDTABLE function to whip it together, but I haven't been able to get it together for one reason or another.
I've attached my pbix if that's of any help, I'm a little stumped. My Pbix Link
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:
5 Replies
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
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.
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:
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"
)
