Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear,
I learned how to build a calendar table by using the following formula:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31))
But it only returns date by date.
I want to build a more detailed date&time table, with 24 hours showed in this table,like the following picture, is there a way to do it in powerbi? Thanks!
Solved! Go to Solution.
Hi @Yin,
Based on my test, you should be able to use the formula below to create a calendar table with date and time.
DateTime = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), UNION ( ROW ( "Time", TIME ( 1, 0, 0 ) ), ROW ( "Time", TIME ( 2, 0, 0 ) ), ROW ( "Time", TIME ( 3, 0, 0 ) ), ROW ( "Time", TIME ( 4, 0, 0 ) ), ROW ( "Time", TIME ( 5, 0, 0 ) ), ROW ( "Time", TIME ( 6, 0, 0 ) ), ROW ( "Time", TIME ( 7, 0, 0 ) ), ROW ( "Time", TIME ( 9, 0, 0 ) ), ROW ( "Time", TIME ( 10, 0, 0 ) ), ROW ( "Time", TIME ( 11, 0, 0 ) ), ROW ( "Time", TIME ( 12, 0, 0 ) ), ROW ( "Time", TIME ( 13, 0, 0 ) ), ROW ( "Time", TIME ( 14, 0, 0 ) ), ROW ( "Time", TIME ( 15, 0, 0 ) ), ROW ( "Time", TIME ( 16, 0, 0 ) ), ROW ( "Time", TIME ( 17, 0, 0 ) ), ROW ( "Time", TIME ( 18, 0, 0 ) ), ROW ( "Time", TIME ( 19, 0, 0 ) ), ROW ( "Time", TIME ( 20, 0, 0 ) ), ROW ( "Time", TIME ( 21, 0, 0 ) ), ROW ( "Time", TIME ( 22, 0, 0 ) ), ROW ( "Time", TIME ( 23, 0, 0 ) ), ROW ( "Time", TIME ( 24, 0, 0 ) ) ) ), "DateTime", [Date] + [Time], "Hour", HOUR ( [Time] ) )
Note: after the table is created, Date and Time column will be formatted as Date/Time type, so you will need to change the Date column to Date type and Time column to Time type.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |