Plot Bar chart for the weeks in the Month (Monday to Sunday)

[BlueWhite3699]

Hi Experts

Is it possible in Power BI to plot a bar chart the starts on Monday and finish on a Sunday for 4 weeks in any given month, so in total i need four charts stacked on top of each other dates runnning from Mon to Sun. The chart must always start on a Mon and end on a Sun - irrespective of the month selected.

[vojtechsima · Accepted solution]

Hello, @BlueWhite3699 

Yes, have a calendar like this:

connect it to your fact table and set up the visual this way:

Please note, some months have 6 weeks technically. If you want just 4 weeks, you have to setup manual logic for that, if you also wanna display week days with no data, so you make sure you always show 7 days, you can click this option on the Axis:

[DataNinja777]

Hi @BlueWhite3699 ,

Yes, you can create a bar chart in Power BI that always starts on Monday and ends on Sunday for four weeks, stacked on top of each other. First, ensure your dataset includes a Date column, a Week Number within the month (1-4), a Day of the Week extracted from the date, and the metric to be plotted (e.g., Hours Worked). You can create a Day of the Week column using the following DAX formula:

DayOfWeek = FORMAT('Table'[Date], "dddd")

To ensure proper sorting from Monday to Sunday, assign a numerical value to each day:

DayOfWeekNumber = WEEKDAY('Table'[Date],2)  // 2 makes Monday = 1, Sunday = 7

Next, to group the dates into four weeks within a month, create a Week of the Month column:

WeekOfMonth = INT((DAY('Table'[Date]) - 1) / 7) + 1

Now, create the bar chart in Power BI. Add DayOfWeek as the X-axis and ensure it is sorted by DayOfWeekNumber to maintain Monday-Sunday order. Add the Hours Worked measure to the Y-axis. To display separate bars for each week, drag WeekOfMonth into the Small Multiples field. This will generate four separate bar charts, each representing a week while maintaining the Monday-Sunday alignment. Alternatively, you can use a Stacked Column Chart by placing WeekOfMonth in the Legend field to show all weeks in a single visual.

To ensure that the filtering of months does not disrupt the Monday-Sunday structure, create a Week Start Date column:

WeekStart = 'Table'[Date] - WEEKDAY('Table'[Date],2) + 1

Sorting the X-axis correctly is crucial to prevent Power BI from defaulting to alphabetical sorting. This can be done by setting DayOfWeek to sort by DayOfWeekNumber. The final output will be a bar chart where each week is consistently displayed from Monday to Sunday, either stacked or in separate panels. Let me know if any refinements are needed!

Best regards,

[Ritaf1983]

Hi @BlueWhite3699 
Yes , please refer to the linked guide :
https://www.youtube.com/watch?v=hJJnyr1qxtk

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[BlueWhite3699]

Hi Thanks for the feedback - i now that element - the hard part is ploting the data.