Relative T-Week scheduled activity filter

[Tim2]

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:

Calendar = GENERATE (

CALENDAR( DATE( YEAR( TODAY() ) - 1, 1, 1), DATE(YEAR( TODAY() ) +1, 12, 31)),

VAR currentDay = [Date]

VAR Work_Week = currentDay-WEEKDAY(currentDay,2) +1

VAR month_year = FORMAT(currentDay,"MMM, YYYY")

VAR year = YEAR ( currentDay )

VAR month_number = MONTH(currentDay)

VAR Relative_Month = (12*YEAR(currentDay)+MONTH(currentDay))-(12*YEAR(TODAY())+MONTH(TODAY()))

Var Abbrev_WW = MONTH(currentDay-WEEKDAY(currentDay,2) +1)&"/"&DAY(currentDay-WEEKDAY(currentDay,2) +1)

RETURN ROW (

"Work Week",Work_Week,

"Abbrev Work Week",Abbrev_WW,

"Calendar Month", month_year,

"Month Number", month_number,

"Relative Month Offset", Relative_Month,

"Calendar Year", year )

)

[v-henryk-mstf · Accepted 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.

[amitchandak]

@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))