How to create a date column in a timetable

[Mattias]

Hi.

I have a time table set up like this.

TimeTable =

var HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23), "Hour", [Value])

var MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value])

var SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])

return

ADDCOLUMNS(

    CROSSJOIN(HourTable, MinuteTable, SecondsTable),

    "Time", TIME([Hour], [Minute], [Second]))

I would like to add a date column with the actual date for the day for each row in order to create a relation with my date table, but I can't seem to figure out how to do it.

any help is appreciated.

Best regards /Mattias

[ValtteriN · Accepted solution]

Hi Mattias,

Here is one way to do this:

TimeTable =

var HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23), "Hour", [Value])

var MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value])

var SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])

return

ADDCOLUMNS(

    CROSSJOIN(HourTable, MinuteTable, SecondsTable),

    "Time", TIME([Hour], [Minute], [Second]),

    "Date",TODAY(),

    "DateTime",FORMAT(TODAY() &" " & TIME([Hour], [Minute], [Second]),"DD/MM/YYYY HH:MM:SS"))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

It worked like a charm! And so simple when you look at it in retro spective, but i couldn't have done it without you, thank you so much