It's the weekend! Or is it?

[lbendlin]

This may come as a shock to you (unless you work in retail), but "weekend" does not equate to "Saturday and Sunday" everywhere. 

Here is a nice overview, courtesy of Wikipedia: https://en.wikipedia.org/wiki/Workweek_and_weekend

As you can see, there are even variations inside a country, not to mention all the shifts that happened over time and that will continue to happen.

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Workweek_and_weekend")),
    Data0 = Source{0}[Data],
    #"Removed Top Rows" = Table.Skip(Data0,1)
in
    #"Removed Top Rows"

Sadly, this source is missing data for some of the larger countries. One example is Uzbekistan (it seems to have a Saturday/Sunday weekend). Other countries have minor spelling differences that will make the later joins difficult. So, we will have to opt for an Excel data source instead, where all the missing data can be added.

Now, let's see how much this actually impacts reporting. Let's add the population numbers of these countries. https://www.worldometers.info/world-population/population-by-country/ has the details we need:

let
    Source = Web.Page(Web.Contents("https://www.worldometers.info/world-population/population-by-country/")),
    Data0 = Source{0}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Data0,{"Country (or dependency)", "Population  (2020)"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Population  (2020)", Int64.Type}})
in
    #"Changed Type"

Now, we can combine these two data sources.  But wait, the workday data is very textual, not really suited for consumption yet. We need to add information to the first query to identify if a particular day (Monday, Tuesday etc.) is considered a workday in that country.

Should we use separate columns for each weekday ?  The advantage is that we can then use binary flags, but the disadvantage is that we need to add seven more columns.  Or maybe we do an enumeration with weekday names or weekday numbers, like "Sunday|Monday|Tuesday|Wednesday|Thursday"  and then use the PATH functions?

While we ponder that issue, let's see if a visual can help us get a first impression.

Away from a line slightly north of the equator, Monday-Friday work weeks prevail, but in the Caribbean, northern Africa and most of the Middle East work weeks have all kinds of different shapes.  The size of the bubbles is representing the population of that country in 2020. 

Let's see if we can chart the impact of this. For simplicity, we assume an 8-hour workday across.  Some countries have Monday-Saturday work weeks, but Saturday is only a half day. The table below uses the approximation with 1 equaling a full 8-hour day and 0.5 equaling a half day at 4 hours. This of course is not very accurate, and we might want to  improve on the model by listing the average hours per day per country instead.

Now, with this information at hand, we can show the "weight"  of each day of the week as a workday based on the population size of the countries that list that day as a workday.

All that's left now is to compare the sum of Monday-Friday to the total value.

Alright, the Monday-Friday workdays are dominant, but maybe it's surprising to see that it tops out at 93% of the world population. Another interesting finding is that Monday through Wednesday are workdays everywhere in the world.

So, what is the solution to the original request "yes, and make it work globally"?

At the minimum, your data needs to support the granularity of the issue. Your dates table needs to have a flag for workday yes/no or the actual hours worked, and if you are operating in a global environment you need to have separate dates tables for the different regions, or use a higher dimension table that holds that information.

Can that table be created and maintained in Power Query or in DAX? Sure. But should it be? Given the complexity described above (and we only barely scratched the surface) that is probably not a good idea.

The better solution is to maintain the reference table(s) outside of Power BI.  Data can be stored in a SQL server table, fed by your company's global data model,  or in a pinch can be stored in an Excel file on a SharePoint. That will give you much more flexibility when you need to adjust to yet another country shuffling their work weekdays around.