Relative date filter; show last 9 (calendar) weeks in graph, with every week starting on Monday

[Anonymous]

Hi All,

I'm new here and this is my first message after a long search and reading. I stumble upon a problem for which I still have not found any solution 😞

Problem: I want to show the last 9 full weeks in my graph, with every week starting from Monday. I've tried this by simply using the relative date filter based on calendar weeks. However, this doesn't show me the all full weeks, neither will it start from Monday.

Method: 

In my report on Power BI Desktop (PBID), I created a custom calendar which includes weeks starting from Monday (notation=2): 

Order_Date =

ADDCOLUMNS (

CALENDAR (DATE(2000,;1,;1); DATE(2025,;12,;31));

"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );

"Year"; YEAR ( [Date] ); "Month"; MONTH([Date]); "Week"; WEEKNUM([Date];2);

"Monthnumber"; FORMAT ( [Date]; "MM" );

"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );

"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );

"MonthNameShort"; FORMAT ( [Date]; "mmm" );

"MonthNameLong"; FORMAT ( [Date]; "mmmm" );

"DayOfWeekNumber"; WEEKDAY ( [Date];2 );

"DayOfWeek"; FORMAT ( [Date]; "dddd" );

"DayOfWeekShort"; FORMAT ( [Date]; "dddd" );

"Quarter"; "Q" & FORMAT ( [Date]; "Q" );

"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )

)

After this, I created a graph showing the data per week, with every week starting nicely on Monday. However, I want this graph to dynamically change when new weeks are being added, showing the last 9 full weeks (so not last 9 weeks in days from now).  I've tried this by using the relative date filter showing the last 9 calendar weeks within my custom calendar.

However, PBID somehow filters the weeks based on Sunday as start of the week, and doesn't take my custom calendar in mind which actually starts on Monday. Therefore the graph shows me the last 9 weeks but with one week (first one) starting from Sunday (which only contains one day). 

If I change my custom calendar back to Sunday, the graph shows full weeks again. 

Hypothesis: I think the relative date filter (function) only works with the default settings (weeks starting from Sunday).

Question: Am I missing something? Is there a way to change the default settings of PBID to 'weeks starting on Monday''? Or is there any other solution for my problem? 

I hope there is a genius out there who can help me out 🙂

Thank you and regrards,

[v-frfei-msft]

Hi @Anonymous,

There is an idea about that, you can vote it up.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20248087-relative-date-filter-week-mon-sun

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft,

Oh really, perfect! Thanks for sharing, I'll definitely vote!

Regards

[LivioLanzo]

hI @Anonymous

would you be able to share some data ?

[Anonymous]

Hi @LivioLanzo

Thanks for your reply! 

Unfortunately I'm not allowed to share the data of my client, sorry. But I can share some images. However, I don't think it's relevant either, I think it has to do with the settings. 

What I can share is the following:

 Graph based on last 9 normal weeksGraph based on last 9 calendar weeks

 As you can see the first graph doesn't give me full weeks, which is logic because it counts back days as of today in weeks. However, the second graph should actually make full calendar weeks, but gives one day (sunday) on week 32. And as you can see both graphs count 10 weeks instead of 9. But the amounts of each week (which are complete) are giving me the correct sums of weeks starting from Monday. 

I hope this makes it a little bit more clear.

Again, thanks for helping LivioLanzo