Creating a calender with date and time in hh:mm:ss

[MirBatman]

Hi All, 

PowerBI newbie here, 

I am trying to create a schedule for my machine downtime. For which I need a calendar with a date and time. This calendar will only be for 2021-2022. The calendar should account for every second. for example:

1/10/2022 1:40:01

1/10/2022 1:40:02

.......

1/10/2022 1:40:59

1/10/2022 1:41:00

Any help will be greatly appreciated!! Thank you all!

[mahoneypat]

Please see this article on splitting your Date and Times and having separate Date and Time tables.

Calculate and Format Durations in DAX – Hoosier BI

And this video on modeling data to calculate machine up/down time.

Simple Model, Simple DAX - Line Time Example - YouTube

Pat

Pat

[aj1973]

Hi @MirBatman 

Creating a Calendar table with every second for every day it is like creating 86400 rows for every day from 2021-2022...it is not a good practice.

What I would recommend is to split the column Date/Time from your fact table into 2 columns Date and Time, add a calendar table either in Power Query or with DAX and then link the 2 tables through Date columns.

[bcdobbs · Accepted solution]

You generally want to split into two separate columns one for date and one for time each with a related dimension. Leaving it all together will make your model massive as power bi can't compress it.

You might find this useful:

https://radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model