A sales report is downloaded from ERP. The dimensions are sales date, product and sales volume.

Does the weekday play a part in sales ? Are the sales for a product different between different weekdays ? Is one day in week more favourable for sales than another one ?

We can do this in several ways.

1. Traditional pivot

If we use traditional Pivot which sits on top of Excel range, we just add weekday next to sales volume. Weekday is defined by the date.

Earlier in my blog I was presenting the IF-sentence which returns you a weekday based on the WEEKDAY function.

=IF(WEEKDAY(a2)=1;”Sunday”;IF(WEEKDAY(a2)=2;”Monday”;IF(WEEKDAY(a2)=3;”Tuesday”;IF(WEEKDAY(a2)=4;”Wednesday”;IF(WEEKDAY(a2)=5;”Thursday”;IF(WEEKDAY(a2)=6;”Friday”;IF(WEEKDAY(a2)=7;”Saturday”)))))))

Create a Pivot based on the range.

Pivot selections.

The results. Weekend is a good time for sales. Friday is still ok, but Monday is really quite day.

2. The weekdays in Power query

However, we can do the same in business intelligence side.

First, we need to download the file to data model.

Transform data.

Close & Load To

Change the date column to date data type.

Add column – date – day – name of day.

Errors appeared.

Select the query options.

Change to Finnish.

Close & Load.

Now the days are ok, at least with me.

Select Add column – custom column.

The formula:

Date.DayOfWeekName([Date])

The columns are the same.

Create a pivot based on the data model.

Selections.

Results not yet sorted.

Different selections.

The same results.

3. Weekday in Power Pivot

Add new column with script below.

=if(WEEKDAY([Date];1)=1;”Sunday”;if(weekday([Date];1)=2;”Monday”; if(weekday([Date];1)=3;”Tuesday”; if(weekday([Date];1)=4;”Wednesday”; if(weekday([Date];1)=5;”Thursday”; if(weekday([Date];1)=6;”Friday”; if(weekday([Date];1)=7;”Saturday”;”Else”)))))))

Selection criterias.

Results in English.

4. Let’s see how the weekdays get reported in Power BI. The dates are in a separate table.

File – get data – text/CSV. To load the flat file.

Press transform data.

The table name was changed.

Select close & apply – close & apply.

Upload the data again, but now upload just the dates. Delete the product and sales volume data by home – remove columns.

Right click the date column header and select remove duplicates.

Select add column – date – day – name of day.

Weekdays appear in Finnish.

If you want to modify the day names, there is also another option.

Select add column – day – day of week.

Day of week returns a number. 6 is Sunday, 0 is Monday, 1 is Tuesday and so on.

Then select add column –  conditional Column.

Fill the IF-sentences via graphical interface. If Day of week (number) equals to 6 then Day short column holds the value Sun and so on.

You can name the days in your own way, I have used here the shortened values.

Close & apply.

Create a simple data model.

Cardinality is one in master data table and many in transaction table.

Create a measure to count the sales.

Sunday is the best sales day, Tuesday the worst. This is in line with Excel reports.

There are many ways to report sales by weekday. Power BI offers graphical tools for several purposes. Also having a data model with transaction table and master data table is useful. Among the options I have demonstrated, I vote for Power BI.