Date table with seconds

[whazaaa]

Hello,

I'm trying to create a table that includes all the dates, hours, minutes and seconds between two set dates. Currently I have this in the DAX : 

DateTable =

ADDCOLUMNS(

        DATESBETWEEN('Date',01/09/2022,01/09/2024),

        TIME(0, 0, 1)

    ,

    "Date",

    FORMAT([Value], "MM/DD/YYYY"),

    "Time",

    FORMAT([Value], "HH:mm:ss")

)

[Ahmedx]

try this

DateTable = 
CROSSJOIN (
    SELECTCOLUMNS (
        GENERATESERIES ( DATE ( 2022, 9, 1 ), DATE ( 2024, 9, 1 ) ),
        "date", [Value]
    ),
    SELECTCOLUMNS (
        GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 0, 1 ) ),
        "time", [Value]
    )
)

[whazaaa]

You gave a good remark regarding the total rows being 64mil. I've added a different query with only the time and added some time slots (5 sec, 1 min, etc etc)

[Ahmedx]

if I understand you correctly there will be a lot of lines, about 64 million

[Idrissshatila · Accepted solution]

Hello @whazaaa ,

refer to this article https://kohera.be/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[whazaaa]

This didn't actually worked but gave me the idea, I've added a timetable with the help of a youtube vid