Make relative dates in Power BI start on Monday
In a video, Ruth Pozuelo Martinez expresses difficulty with a common issue in Microsoft Power BI related to date filters. The primary problem is that Power BI, by default, starts the week on Sunday, aligning with U.S. norms but not with the global standard of starting weeks on Monday. This discrepancy often causes a problem, particularly when trying to filter data using "date to do relative dates" and the "date filter".
- This issue of weeks starting on Sunday is cited as frustrating and even disrespectful to users outside the United States.
- Ruth demonstrates this by trying to filter sales data for the 'last week' from a database, where the week still starts on Sunday.
- Since there's no direct way to correct this in Power BI, she suggests a workaround to solve this.
- This workaround involves creating a custom column specifying if a date falls in the previous week.
- However, she points out that while this workaround is effective, it burdens the system's performance and is therefore not a permanent solution.
- Relative dates are useful for creating reports that always reflect the most current data, yet they refer to the same period of time (like the last seven days or the next three months).
When we talk about making relative dates start on Monday, we refer to the "week-based" calculations. The week start day in Power BI is determined by the data source settings or locale, which is often set to Sunday in many English-speaking countries.
There's no built-in way to adjust the first day of the week in Power BI. However, you can achieve this by creating a new column in Power Query or using DAX (Data Analysis Expressions).
Here's an example using DAX:
Let's say you have a Date column in your data, you can create a new Calculated Column using the following DAX formula:
dax Copy code WeekStartingMonday = 'Table'[Date] - WEEKDAY('Table'[Date], 2) + 1 The WEEKDAY function returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. By providing a second parameter as 2, you're asking the WEEKDAY function to return the day of the week where Monday is 1 and Sunday is 7. By subtracting this value from the actual date and adding 1, you can calculate the date of the start of the week (Monday) corresponding to any given date.
Now you have a new column that treats Monday as the first day of the week. You can use this column to build your visuals or further calculations.
Deep Dive into the Issue and Its Impact
This recurring discrepancy in Microsoft's Power BI, of weeks starting on Sunday instead of Monday, may seem minor but it significantly impacts users. It hinders the effective filtering of data, proving a constant annoyance. The obligatory workaround, though effective, adversely impacts system performance. Furthermore, it does not correspond to global norms, a factor that could tarnish the software reputation for users outside of the United States.
Learn about Make relative dates in Power BI start on Monday
To make relative dates in Microsoft Power BI start on Monday, a user can create a custom column that indicates whether a date is in the previous week. This workaround is effective but can be a drain on the system's performance. To avoid this, users should look for other solutions, such as altering the program's settings or using a different software. Additionally, users should be aware that, by default, Power BI considers weeks to start on Sunday, which is a standard mainly followed in the United States, and is at odds with the global norm of weeks starting on Monday. Microsoft should consider the global norm when creating software, as it is disrespectful to users outside the U.S. When using Power BI, users should be aware of how to adjust the program to start weeks on Monday, or use a workaround to create a custom column. Other solutions may be available, and the user should research these options before using the custom column workaround. It is important for Microsoft to consider the global norm when creating software, and to be respectful to all users.
More links on about Make relative dates in Power BI start on Monday
- How to set build-in Relative Date filter to use mo...
- Jun 4, 2023 — So the filter appears to be filtering the date table to a Monday-Sunday week which is then appropriately "offsetting" all my other measures since they reference ...
- Relative Dates - Start Monday - Microsoft Power BI Community
- Feb 6, 2021 — Hi I really need to know how to set my relative start dates to a Monday. My regional settings are set to UK whch starts Monday, but whenever I add a.
- Date Slicer changing when week begins - DAX Calculations
- Nov 12, 2020 — Hi So my week is Monday - Sunday and my date table confirms for example the start of the week is 9th Nov and End of Week is 15th Nov.
- Relative Date Slicer for Your Local Time Zone in Power BI
- May 11, 2020 — Relative Date Slicer for Your Local Time Zone in Power BI ... Monday, // set the week's start day, values: Day.Monday, Day, Sunday.
- Is there a way to have relative date filter start from 1st day ...
- Mar 21, 2023 — Create a column using DAX: diff = DATEDIFF ( TODAY(), [Created On], MONTH). 2. Drag 'diff' into visual filter and set Advanced ...
- Relative date filter on calendar week not calculating last ...
- Feb 12, 2021 · 1 answerCurrently, calendar week is defined Sunday to Saturday when it comes to filtering using relative dates.
- Using relative date filters in Power BI to automate recurring ...
- Dec 23, 2022 — For example, you can use the relative date filter to show only sales data that's happened within the last 30 days (or month, calendar months, ...
- Calculate Relative Weeks in Power BI
- In this tip we will explore how to generate the Relative Week Number using both SQL and DAX for Power BI reports.
Keywords
"Power BI relative dates start Monday", "Power BI date filter start Monday", "Microsoft Power BI start week Monday", "Change Power BI relative dates to Monday", "Power BI dates starting on Monday"