Change Start of Week Day Dynamically

[cchilton]

Hello, 

Context: Our organization is building an application that incorporates Power BI analytics. Our customers are from different companies that start their work week on a different day. 

Issue: I am unable to dynamically change the start day of the work week based on the company selected. 

Data design:

What I have tried:
- I created a new date column to manipulate the start day of the work week based on the company selected. Please see below 

DAX

Result (does not work as expected) 

DAX hard coded (works as expected) 

Desired Result (this should change based on the company selected) 

*Note: 

The numbers 1 - 7 represent the start day of the week. Where 1 = Sunday and 7 = Saturday. I found that if I add 1, 2, 3 days etc to VAR RESULT  I get the desired output. The issue is that the variable "StartOfWkConversion" does not work as expected. The work week is always Sunday - Saturday. 

Does anyone have a good solution to this problem? I am fairly new to DAX. This is the best solution I could come up with thus far. 

[Muhammad110]

So the thing is, your customers come from different companies, and each company starts their work week on a different day. Now, you want to dynamically change the start day of the work week based on the selected company.

So in your data model, make sure you have a column that tells the start day of the work week for each company. This could be a separate column in your company table or maybe a relationship with a calendar table. You know, whatever works for you. 

then use the below measure 

Dynamic Start Day =
SWITCH (
SELECTEDVALUE ( CompanyTable[Company] ),
"Company A", CompanyTable[StartDay_A],
"Company B", CompanyTable[StartDay_B],
"Company C", CompanyTable[StartDay_C],
)

[cchilton]

Thank you for your prompt reply. This works. In fact, I tried this method as well. The issue is that when I add it to my "DateNew" column the switch statement returns blank so it does not work for my purposes.