Forum Discussion
Solved
How to create a date column in a timetable
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
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])returnADDCOLUMNS(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/
2 Replies
- ValtteriN
Community Champion
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])returnADDCOLUMNS(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/
