how to change calendar week start date from Sun to Mon

[erdinch]

Hello,

I have a measure that is showing results week by week.

below that  measure I have another one showing results days by day within a week (Mon, Tue...Sun)

For that visuals I have used relative date filtering (last 1 calendar week)

it is showing the data by day (Mon, Tuem, Wed, Thu, Fri, Sat, Sun) but unfortuanetele the sunday is not the day after sat but the day before Monday :

Mon (27.01), Tue(28.01), Wed(29.01), Thu (30.01), Fri(31.01), Sat (01.02), Sun (26.01)

I guess that is coming from special setting somewhere as the time slot mention in the date filer for the visual is showing (26.01.2020-01.02.2020) which in fact is Sun-Mon. Printscreen is attached.

coulf you please advice me how to change that from Sun-Mon to Mon-Sun?

Thanks!

[AlexColat]

To anyone this can help found the solution here: Relative Date Dimension - Microsoft Power BI Community

In order for the week start day to be Monday change 

#"Added rWeek" = Table.AddColumn( #"Added Week", "rWeek", each Date.WeekOfYear( [Date] ) - #"This Week", Int64.Type ),

to

#"Added rWeek" = Table.AddColumn( #"Added Week", "rWeek", each Date.WeekOfYear( [Date], Day.Monday ) - #"This Week", Int64.Type ),

And filter your page or visual on rWeek is 1 to get the current week starting on Monday. 

[Anonymous]

Better and quick one - Add a calculated column with a condition 

Last_week_manual_filter = IF(
                 WEEKNUM(Date_Table[Date],"21")=MAXX(Date_Table,WEEKNUM(Date_Table[Date],"21"))-1,1,0)
All you need to do after is to filter your visual with Last_week_manual_filter=1 
Hope this help

[bhagyashripatil · Accepted solution]

Hi @erdinch ,

Please find below the steps for your required result:

(1) Its the basic default way of Power BI to assign Week_Day_No as '0' to Mon, but thats not the issue. 

So as per my understanding, currently you are trying to apply Relative date filter using the respective Date field (where filter is "is in the last > 1 > calender week"

(2) Now for getting the result you need, all you need is just a Calculated Column stating:

WeekStartDate = 'Date_Table'[Date] - WEEKDAY(Date_Table[Date] , 2) + 1

(Note: "WEEKDAY(Date_Table[Date] , 2) + 1" -> this will increment the week number index by 1. So that will hell including the upcoming Sunday in that week instead of previous Sunday.)

(3) Now, try replacing the previous "Relative filter date field" by this "Calculated Column field". Give same Relative date filter on field WeekStartDate as -> where filter is "is in the last > 1 > calender week"

Please find below the visual after applying this filter. This should definitely solve your problem.

Happy Learning !!

Bhagyashri 

[GT81]

Hi all,

This didn't work for me. What am I doing wrong?
I have created that calculated column and the week start date column is right in my calendar table but when I use this column as a relative date filter(last calendar week), it is still showing the wrong start date

Thanks,
Regards

[MHAO]

did you find an answer for your post please?

[TechR21]

Hello,

this helped me also thank you 🙂

question;

I have created a column to start the week at friday with the following:

Week Start date(Fri-Thu) = 'Incidents'[created]+-1*if(WEEKDAY(Incidents[created])<6,WEEKDAY('Incidents'[created])+1,WEEKDAY('Incidents'[created])-6 )

But i want to include a specific time so that a week starts at fridays 17:00:00 and ends at the following friday at 16:59:59

Any idea?

[pborah]

One more kudos for you as it helped me as well.

[PRAMODMANJUNATH]

Hi @bhagyashripatil ,

Thanks, your solution really helped.

Amazing logic 🙂

[Anonymous]

Hello bhagyashripatil,

I'm not super versed on developing in PBI, my effort is always the triple than everyoneelse. Regarding your answer, I tried to add a new Custom colum to my Query and the formula seems not to be able to be applied. How did you actually entered the Colum so then to add the easy formula. I know id a dumb question, but....oh well 😃

Kind Regards

Leo

[bhagyashripatil]

Hello @Anonymous, 

I have created the Calculated Column at report side, and not at query side. So try creating calculated column at report side instead.

By the way, is there any specific reason, why you want this column at query side ?

Regards,

Bhagyashri

[Anonymous]

Not at all, as I mentioned, usually this is part of why do I needs to put 3 times de effort than everybody else. Trail and error =).

Thanks

[bhagyashripatil]

Hello @Anonymous 

No worries.

You can get an overview of creating 'Calculated Column' in power bi at report side, on this Link . I hope this gives you insight on how to use the above Week Number calculation using calculated column.

Regards,

Bhagyashri

[erdinch]

dear bhagyashripatil,

Thanks a lot for the hint. It worked!!!

[bhagyashripatil]

Welcome @erdinch ! Glad it helped you. 

[MartynRamsden]

Hi @erdinch 

It's not currently possible to change this behaviour.

There's an idea for it here. Be sure to vote for it.

If you have a date table, you could add a calculated column to identify the dates which you want to include as 'last week'.

You could then use this column as a visual level filter, rather than the relative date filter.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[jfox0603]

Thank you Martyn. The previous "solutions" are inconvenient workarounds that become unwieldy as more and more dates are added. Please help vote this idea up.