topic how do I create a date table with the date format YYYY-MM-DD 00:00 real time data, and weeknr in DAX Commands and Tips

how do I create a date table with the date format YYYY-MM-DD 00:00 real time data, and weeknr

SLJN — Tue, 13 Dec 2022 19:32:03 GMT

Hi!

Im trying to create a date table with timestamp and correct weeknumber (monday as first day). But I'm stuck.

Date =

VAR MinDate = DATE(YEAR(TODAY())-2,"-01-01", NOW)
VAR MAxDate = YEAR(TODAY(),NOW)

ADDCOLUMNS (
FILTER(
CALENDERAUTO(),
YEAR ([Date]) >= MinDate &&
YEAR ([Date]) <= MaxDate,

"Year" ,YEAR ( [Date] ),

MONTH, FORMAT ([Date], "m" ),

"MonthNr", FORMAT ( [Date], "MM" ),

"Day", DAY ( [Date] ),

"WEEKDAY([Date],2)

)

I can't seem to get it right, how should I solve this?

Re: how do I create a date table with the date format YYYY-MM-DD 00:00 real time data, and weeknr

AUaero — Tue, 13 Dec 2022 20:43:08 GMT

The DAX you posted isn't valid - maybe a copy/paste error?

The code below should get you what you need. As far as formatting the date field as yyyy-mm-dd hh:mm:ss, you can specify the format of the column after you create the table.

Date = VAR MinDate = DATE(YEAR(TODAY())-2, 1, 1) VAR MaxDate = TODAY() RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Year", YEAR([Date]), "MonthNr", FORMAT([Date], "MM"), "Day", DAY ([Date]), "DOW", WEEKDAY([Date],2), "WeekNumber", WEEKNUM([Date], 1) )