cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AC1
Frequent Visitor

User to Select Week Start Day

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: User to Select Week Start Day

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

 

99.PNG

 

Then create a calculated table to CROSSJOIN above two tables:

 

Table = CROSSJOIN('Weekday',FirstWeekDaySelection)

It will be like:

 

 

6666.PNG

 

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
)

44.PNG

 

 

Regards,

View solution in original post

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: User to Select Week Start Day

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

 

99.PNG

 

Then create a calculated table to CROSSJOIN above two tables:

 

Table = CROSSJOIN('Weekday',FirstWeekDaySelection)

It will be like:

 

 

6666.PNG

 

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
)

44.PNG

 

 

Regards,

View solution in original post

Highlighted
AC1
Frequent Visitor

Re: User to Select Week Start Day

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors