The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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?
Solved! Go to Solution.
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)
)
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)
)
User | Count |
---|---|
24 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
9 | |
9 |