Forum Discussion

AC1's avatar
AC1
Advocate II
9 years ago
Solved

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 t...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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's avatar
AC1
Advocate II
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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?