First day of the week

[Milozebre]

Good afternoon, 

I have an issue when I try to use filter on a relative date. 

I choose week and the first day of the week is monday. 

I write this on my sql queries and in the table the results are good : 

, CASE WHEN datepart(dw ,a.D_APPEL) = 1 THEN 'Lundi'
WHEN datepart(dw ,a.D_APPEL) = 2 THEN 'Mardi'
WHEN datepart(dw ,a.D_APPEL) = 3 THEN 'Mercredi'
WHEN datepart(dw ,a.D_APPEL) = 4 THEN 'Jeudi'
WHEN datepart(dw ,a.D_APPEL) = 5 THEN 'Vendredi'
WHEN datepart(dw ,a.D_APPEL) = 6 THEN 'Samedi'
WHEN datepart(dw ,a.D_APPEL) = 7 THEN 'Dimanche'

Im living in france and I use regional settings (france). 

How can I change this to obtain monday as a first day of the week. 

Thank you

[Greg_Deckler · Accepted solution]

Have a look at my quick measure here for first working day, first day, etc. Should be able to modify these for what you want specifically.

https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487

For example, you could use the second one, Week Starting and just add an additional filter for Monday (WEEKDAY()).

[Milozebre]

good afternoon Greg, 

Thank your tips. Its an awesome job. 

I check your pbix Weekending and  how can I include weekstarting. 

for mweekStarting i put your mesure 

mWeekStarting 

But you have a column WeekEnding (see below your measure) and I presume that I need WeekStarting. Can you help me with it ? 

WeekEnding =
VAR myWeekNum = WEEKNUM([Date])
VAR myYear = YEAR([Date])
VAR myEndDate = CALCULATE(MAX([Date]);FILTER(ALL(Calender);YEAR([Date])=myYear && Calender[WeekNum]=myWeekNum && Calender[WeekDay]=7))
VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate));myEndDate;CALCULATE(MAX([Date]);FILTER(ALL(Calender);YEAR([Date])=myYear+1 && Calender[WeekNum]=1&&Calender[WeekDay]=7)))
VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1));myEndDate1;MAX([Date]))
RETURN "W" & myWeekNum & " Week Ending " & myEndDate2 

Thank you 

[Greg_Deckler]

WeekStarting = 
VAR myWeekNum = WEEKNUM([Date])
VAR myYear = YEAR([Date])
VAR myEndDate = CALCULATE(MIN([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear && Calender[WeekNum]=myWeekNum && Calender[WeekDay]=1))
VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate)),myEndDate,CALCULATE(MIN([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear-1 && Calender[WeekNum]=53&&Calender[WeekDay]=1)))
VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1)),myEndDate1,CALCULATE(MIN([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear-1 && Calender[WeekNum]=52&&Calender[WeekDay]=1)))
VAR myEndDate3 = IF(NOT(ISBLANK(myEndDate1)),myEndDate2,MIN([Date]))
RETURN "W" & myWeekNum & " Week Ending " & myEndDate3