Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have been trying to develop a filter\slicer to display scheduled activities by relative "Work Week" , ie T-1 would be activities scheduled to start on the next Monday through next Sunday, T-2 would be activities scheduled to start the Monday after next through the Sunday after next, and so on and so forth out 26 weeks. I would only need to see the data for the 7 days of the selected work week. The field I am using for scheduled activities is called [Scheduled Start].
I have attempted to add a column to my Calendar table to enable this but so far have been unsuccessful. Any and all assistance is greatly appreciated!
My current calendar table for reference:
Solved! Go to Solution.
Hi @Tim2 ,
I created a similar calendar model, which may be used as a reference.
VAR WeekNumberType = 2
// The second parameter type of WEEKNUM controls the start time of each week and returns the number of the week in the year
// 1, the week starts on Sunday
// 2, the week starts on Monday
VAR WeekDayType = 2
// The second parameter type of WEEKDAY controls the start time of the week and returns the number of the day of the week
// 1, the week starts on Sunday (1) and ends on Saturday (7), numbered 1 to 7
// 2, the week starts on Monday (1) and ends on Sunday (7), numbered 1 to 7
// 3, the week starts on Monday (0) and ends on Sunday (6), numbered 0 to 6
Calendar__ =
VAR YearStart = 2019
VAR YearEnd = 2020
VAR WeekNumberType = 2
VAR WeekDayType = 2
RETURN
GENERATE (
CALENDAR( DATE( YearStart , 1 , 1 ) , DATE( YearEnd , 12 , 31 ) ),
VAR Year = YEAR ( [Date] )
VAR Month = MONTH ( [Date] )
VAR Quarter = QUARTER( [Date] )
VAR Day = DAY( [Date] )
VAR YearMonth = Year * 100 + Month
VAR Weekday = WEEKDAY( [Date] , WeekDayType )
VAR WeekOfYear = WEEKNUM( [Date] , WeekNumberType )
RETURN ROW (
"Year" , Year ,
"Quarter" , Quarter ,
"Month" , Month ,
"Day" , Day ,
"Y_Name" , "Y" & Year ,
"Q_Name" , "Q" & Quarter ,
"Y_Q", Year & "Q" & Quarter ,
"YQ_serial number " , ( Year - YearStart )*4 + Quarter,
"M_Name", FORMAT ( [Date], "MMM" ) ,
"YearMonth" , YearMonth ,
"YM_serial number " , ( Year - YearStart )*12 + Month,
"YQ_Day" , INT( [Date] - DATE( Year , 1 , 1 ) + 1 ),
"Weekday" , Weekday ,
"W_Name" , FORMAT( [Date] , "DDD" ) ,
"Y_Week" , WeekOfYear ,
"YW_serial number ", "W" & RIGHT( 0 & WeekOfYear , 2 ) ,
"YW" , Year & "W" & RIGHT( 0 & WeekOfYear , 2 ) ,
"Date_" , Year * 10000 + Month * 100 + Day
))
Hope to help you.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Tim2 ,
I created a similar calendar model, which may be used as a reference.
VAR WeekNumberType = 2
// The second parameter type of WEEKNUM controls the start time of each week and returns the number of the week in the year
// 1, the week starts on Sunday
// 2, the week starts on Monday
VAR WeekDayType = 2
// The second parameter type of WEEKDAY controls the start time of the week and returns the number of the day of the week
// 1, the week starts on Sunday (1) and ends on Saturday (7), numbered 1 to 7
// 2, the week starts on Monday (1) and ends on Sunday (7), numbered 1 to 7
// 3, the week starts on Monday (0) and ends on Sunday (6), numbered 0 to 6
Calendar__ =
VAR YearStart = 2019
VAR YearEnd = 2020
VAR WeekNumberType = 2
VAR WeekDayType = 2
RETURN
GENERATE (
CALENDAR( DATE( YearStart , 1 , 1 ) , DATE( YearEnd , 12 , 31 ) ),
VAR Year = YEAR ( [Date] )
VAR Month = MONTH ( [Date] )
VAR Quarter = QUARTER( [Date] )
VAR Day = DAY( [Date] )
VAR YearMonth = Year * 100 + Month
VAR Weekday = WEEKDAY( [Date] , WeekDayType )
VAR WeekOfYear = WEEKNUM( [Date] , WeekNumberType )
RETURN ROW (
"Year" , Year ,
"Quarter" , Quarter ,
"Month" , Month ,
"Day" , Day ,
"Y_Name" , "Y" & Year ,
"Q_Name" , "Q" & Quarter ,
"Y_Q", Year & "Q" & Quarter ,
"YQ_serial number " , ( Year - YearStart )*4 + Quarter,
"M_Name", FORMAT ( [Date], "MMM" ) ,
"YearMonth" , YearMonth ,
"YM_serial number " , ( Year - YearStart )*12 + Month,
"YQ_Day" , INT( [Date] - DATE( Year , 1 , 1 ) + 1 ),
"Weekday" , Weekday ,
"W_Name" , FORMAT( [Date] , "DDD" ) ,
"Y_Week" , WeekOfYear ,
"YW_serial number ", "W" & RIGHT( 0 & WeekOfYear , 2 ) ,
"YW" , Year & "W" & RIGHT( 0 & WeekOfYear , 2 ) ,
"Date_" , Year * 10000 + Month * 100 + Day
))
Hope to help you.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Tim2 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Usually we can these column and can get this week last week
new columns
Week Start date = "Date"[Date]+-1*WEEKDAY("Date"[Date],2)+1
Week End date = "Date"[Date]+ 7-1*WEEKDAY("Date"[Date],2)
Week Rank = RANKX(all("Date"),"Date"[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all("Date"),"Date"[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]=max("Date"[Week Rank])))
Last Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]=max("Date"[Week Rank])-1))
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |