topic Re: How to create a date column in a timetable in DAX Commands and Tips

How to create a date column in a timetable

Mattias — Thu, 02 Feb 2023 08:33:21 GMT

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

Re: How to create a date column in a timetable

ValtteriN — Thu, 02 Feb 2023 08:44:11 GMT

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/

Re: How to create a date column in a timetable

Anonymous — Mon, 06 Feb 2023 05:27:46 GMT

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