Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the below Date table, i want to add a new column which shows startOftheWeek date(in my case start of the week should be every thursday).
How can i calculate the start of the week(every thursday) and show that in the newly created column in the Date table below.
Date = ADDCOLUMNS ( CALENDARAUTO (); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "Monthnumber"; FORMAT ( [Date]; "MM" ); "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthNameShort"; FORMAT ( [Date]; "mmm" ); "MonthNameLong"; FORMAT ( [Date]; "mmmm" ); "DayOfWeekNumber"; WEEKDAY ( [Date] ); "DayOfWeek"; FORMAT ( [Date]; "dddd" ); "DayOfWeekShort"; FORMAT ( [Date]; "ddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ) )
Solved! Go to Solution.
Something like below. Apply formatting as required.
Date = ADDCOLUMNS ( CALENDARAUTO (); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "Monthnumber"; FORMAT ( [Date]; "MM" ); "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthNameShort"; FORMAT ( [Date]; "mmm" ); "MonthNameLong"; FORMAT ( [Date]; "mmmm" ); "DayOfWeekNumber"; WEEKDAY ( [Date] ); "DayOfWeek"; FORMAT ( [Date]; "dddd" ); "DayOfWeekShort"; FORMAT ( [Date]; "ddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ); "startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1 )
Something like below. Apply formatting as required.
Date = ADDCOLUMNS ( CALENDARAUTO (); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "Monthnumber"; FORMAT ( [Date]; "MM" ); "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthNameShort"; FORMAT ( [Date]; "mmm" ); "MonthNameLong"; FORMAT ( [Date]; "mmmm" ); "DayOfWeekNumber"; WEEKDAY ( [Date] ); "DayOfWeek"; FORMAT ( [Date]; "dddd" ); "DayOfWeekShort"; FORMAT ( [Date]; "ddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ); "startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1 )
What does this line do, can you please elloborate. Why are you substracting WEEKDAY([Date],14)+1?
"startOftheWeek"; [Date] - WEEKDAY ( [Date], 14) + 1
Sorry, i could not understand why are you using 14. As in this link https://msdn.microsoft.com/en-us/query-bi/dax/weekday-function-dax, they specified WEEKDAY(<date>, <return_type>) and return_type is either 1 or 2 or 3.
That documentation isn't complete.
Argument can use following values.
1 to 3 and 11 to 17.
11 to 17 is useful when you want week to start on specific weekday.
11 = Monday = 1
12 = Tuesday = 1
...
14 = Thursday = 1
...
You get the idea.
@Chihiro, Can i create a new measure which shows thursday's date as the weekstart date in my existing productTable which has ID,prodName,status,DeliveredDate,Comments as the columns. The before accepted solution is great but we are creating a separate Date table instead of it i just want to create one calculated column or measure in my existing table if possible?
User | Count |
---|---|
94 | |
78 | |
73 | |
63 | |
60 |
User | Count |
---|---|
108 | |
101 | |
77 | |
63 | |
61 |