Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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()).
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()).
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
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