The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Evening,
I'm trying to show on my axis the time of the day in 20 minute intervals. At the moment I have a time table which has the time by the second as well as a second column which I've extracted the time by the start of the hour.
I was wondering if anyone new of a way to format or configure as to show for example:
7.00am-7.20am
7.20am-7.40am
7.40am-8.00am.
My best thought was to use a conditional column in order to calculate this; but considering my time column in terms of granuality the highest possible would be "7:21am"; I would I have to do If 'time' contains "7.21am" = 7.40am. And even then, I'm not sure I would be able to filter and sort it appropriatly.
Thoughts and feelings?
Solved! Go to Solution.
How about
- You create a new "TimeSlot" column in your time table that calculates the time slot
TimeSlot =
SWITCH(
TRUE(),
HourMinuteSecond[Minute] < 20,HourMinuteSecond[Hour12]& ".00"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".19"&HourMinuteSecond[AMPM],
HourMinuteSecond[Minute] < 40,HourMinuteSecond[Hour12]& ".20"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".39"&HourMinuteSecond[AMPM],
HourMinuteSecond[Hour12]& ".40"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".59"&HourMinuteSecond[AMPM]
)
- To make sure the timeslots are ordered correctly you need another column calculating a sortorder. someting like
TimeSlotSortOrder = HourMinuteSecond[Hour24] * 100 + SWITCH( TRUE(), HourMinuteSecond[Minute] < 20,1, HourMinuteSecond[Minute] < 40,2, 3 )
- Now you can set the "Sort by Column" property of the "TimeSlot" column to the "TimeSlotSortOrder" column
Hope this helps
JJ
I don't fully understand your post - let me admit that up front.
Your static time table has 24 x 60 x 60 = 86400 records I believe. And it sounds like you have 3 x 24 = 72 unique intervals in a day - and so there is a column in your static time table for the unique Interval ID and in each record of the table one of the 72 unique interval ID values is entered.
Then in your data set table there is a time value field that can join to the time value field in the static time table - and you will have the adjoining field of the Interval ID available to you for reporting.
How about
- You create a new "TimeSlot" column in your time table that calculates the time slot
TimeSlot =
SWITCH(
TRUE(),
HourMinuteSecond[Minute] < 20,HourMinuteSecond[Hour12]& ".00"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".19"&HourMinuteSecond[AMPM],
HourMinuteSecond[Minute] < 40,HourMinuteSecond[Hour12]& ".20"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".39"&HourMinuteSecond[AMPM],
HourMinuteSecond[Hour12]& ".40"& HourMinuteSecond[AMPM]&"-"&HourMinuteSecond[Hour12]&".59"&HourMinuteSecond[AMPM]
)
- To make sure the timeslots are ordered correctly you need another column calculating a sortorder. someting like
TimeSlotSortOrder = HourMinuteSecond[Hour24] * 100 + SWITCH( TRUE(), HourMinuteSecond[Minute] < 20,1, HourMinuteSecond[Minute] < 40,2, 3 )
- Now you can set the "Sort by Column" property of the "TimeSlot" column to the "TimeSlotSortOrder" column
Hope this helps
JJ
Morning,
Thank you so much for the response. The TimeSlot column works and is 100% what I was looking for thank you so much.
When I tried to the sort the Timeslot column by the Timeslotsortorder column it gave me:
https://gyazo.com/2d7826c15b41e0f29617262e64edddb6
Which is a bit bizare; I sorted it by the Hour12 column and its filtering perfectly; thank you so much.
Hi Elliot
Glad I could help!
You have to make sure that entries that make up the order column are unique. I checked your sample data: there are several entries with identical values for hour and minute (starting at 11 am). I guess thats the reason for that message.
JJ
I'm trying to do a 30min slot, I've got the TimeSlot measure working perfectly (thank you), but I'm unable to create a TimeSlotOrder to work with it. I'm not sure how to do it as to avoid the typical sorting error. Thoughts?
This next part would probably be better suited to it's own post; but I'll post it here to see how it travels.
How do you think we would be able to create a rolling time window based upon sales.
As so for example; we could find the 30minute window on Monday where the most sales occured within a thirty minute window, so it might be 11.21-11.51am or 2.08-2.38pm for example?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |