User to Select Week Start Day

[AC1]

Hi,

I wish for the user (through a slicer) to select either Monday, Tuesday, etc. as the day of which we set the week to start. Then apply this on a date range in another table.

I have come this far:

1. Created slicer table:

Week_Day

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

2. Created Slicer.

3. Created output from slicer through either of these 2 options:

Week Day Selected Option 1 = 
IF (
    ISFILTERED (tbl_WeekDays[Week_Day]) && HASONEVALUE (tbl_WeekDays[Week_Day]),
    LASTNONBLANK (tbl_WeekDays[Week_Day], 0),
    "NONE"
)

Week Day Selected Option 2 = 
IF(
   HASONEVALUE(tbl_WeekDays[Week_Day]), 
   VALUES(tbl_WeekDays[Week_Day]),
   "NONE"
)

4. Created output of slicer to readable input for WEEKNUM:

Week Day Number Selected = 
IF(
   HASONEVALUE (tbl_WeekDays[Week_Day]),
   SWITCH(
          VALUES (tbl_WeekDays[Week_Day]),
          "Monday",    11,
          "Tuesday",   12,
          "Wednesday", 13,
          "Thursday",  14,
          "Friday",    15,
          "Saturday",  16,
          "Sunday",    17
   ),
   15
)

5. Use the above output in WEEKNUM:

Week (Dynamic) = WEEKNUM(tbl_Data[Date], [Week Day Number Selected])

Points 1-4 works fine, but 5 does not, as the week start day does not change when the user change the day in the slicer. There are no errors coming up when createing point 5.

Points 3-4 have been created as a measure, while 5 (as far as I can see) needs to be a column.

Points 3-5 have all been created under the table where we wish to apply the WEEKNUM onto its date range.

I was hoping someone might be able to help me make point 5 work.

I have a .pbix to share, but don't know how to share it here.

Cheers,

Alex

[v-sihou-msft · Accepted solution]

@AC1

According to your desription, you want to display the dynamic "Fiscal Weekday" based on the selection from users to determine which day is the first day of the week. Right?

In this scenario, you can create another table for user selecting which day as first day. I assume you have two tables like below:

Then create a calculated table to CROSSJOIN above two tables:

Table = CROSSJOIN('Weekday',FirstWeekDaySelection)

It will be like:

Now you just need to create a calculated column to show the dynamic Fiscal Weekday.

Fiscal Weekday =
IF (
    'Table'[WeekDay] >= 'Table'[FirstWeekdaySelection],
    'Table'[WeekDay] - 'Table'[FirstWeekdaySelection]
        + 1,
    'Table'[WeekDay] - 'Table'[FirstWeekdaySelection]
        + 8
)

Regards,

[AC1]

Hi,

Thanks for your input.

I think I understand your solution so will try later today to implement it.

I will keep you posted.

Thanks again for your help in this matter.

Cheers,

Alex

[JeroenR]

@AC1 did you resolved this using the explanation? 

@v-sihou-msft I do not fully understand your solution. Can you also this functionality to a Date / Calendar table?