Does a date fall on a weekend in Power BI

Sometimes it can be useful to know whether a certain date falls on a weekend or not. You may want to know how often a certain activity takes place on weekends compared to weekdays. In Power BI, it is quite easy to determine the day based on a date. In this article, I'll show you how to do it easily.

We use a dataset with some dates and activities (in this case sports). Based on the date, we will see how often a sport occurs during the weekend.

At the bottom of this page you can download the example file with source file to download so that you can follow everything step by step.

Retrieving data from a source

Start Power BI and create a new report.
We retrieve our data from the Excel file Aktivities.xlsx. To do this, go to in the Ribbon Start and choose Import data. Choose from the menu excel workbook and then click the button Make a connection.

Go to the folder where the Excel file Aktivities.xlsx is stored and select the file. Press the button To open to import the data.

Check the box Rows and then click the button Transform data.

You should have imported the following columns:

• ID;
• Date;
• Time of day;
• Activity.

The data types should already be set correctly. The date must in any case be set as the Date data type.

Determine the day of the week based on the date

To determine the day of the week we add a column to our dataset.

In the Power Query Editor, go to the tab Add column and click Custom column.

A new window will open. Enter the name “Custom” in the text field New column name to “Weekday”.

Fill in the Custom column formula enter the following M code:

Date.DayOfWeekName([Date],"NL-nl")

Then click OK.

An extra column has now been created in which the weekdays are listed that belong to the relevant date.

[image]

Place data in a visual

Exit the Power Query Editor by clicking the button Close and apply to click. We then come to the main Power BI window.

Select from the pane Visualizations the Matrix-visual.

Then drag from the pane Fields the field:

• Date nasty Rows
• Weekday nasty Columns
• Activity nasty Values

In the matrix table, the day of the week is displayed per column. It is striking that this is not set in order of the weekdays, but in alphabetical order. So the next step is to put the days of the week in the correct order.

Arrange weekdays in order

To put the weekdays in the correct order, we need to make a helper column with day numbers in the data table.

To determine the day number from a date, we use the function Date.DayOfWeek.

Open the Power Query Editor by clicking the button in the main window Transform data.

In the Power Query editor, go to Add column and click Custom column.

Match New column name Amended to to Day number.

fill in Custom column formula the following function:

Date.DayOfWeek([Date], 1)

Pass of the column Day number the data type to Round number.

Explanation of function Date.DayOfWeek

The function Date.DayOfWeek returns a number from 0 to 6 based on an entered date. The function has two arguments:

• dateTime (which data type must be date, datetime or datetimezone);
• firstDayOfWeek (whose data type is a numeric number).

Power BI sees Sunday as the first day of the week (day 0) and Saturday as the last day of the week (day 6). In the Netherlands, Monday (day 1) is the first day of the week. On to the argument firstdayOfWeek Entering the number 1 indicates that Monday is the first day of the week.

Sort weekdays by day number

Exit the Power Query Editor by pressing the button Close and apply to click.

On the left side of the screen, select the pane Facts.

Select the column Day number.

Click in the Ribbon in the tab Column Tools on the button Sort by column.

Choose the column from the drop-down menu Day number.

Click on the left side of your screen Report to go to the pane Report to go. You will now see that all days are sorted by day number.

Downloads

If you can't figure it out with the above guide alone, download the sample file or ask a question by leaving a message.