Power BI Business Day Reporting

Reporting daily sales and month-to-date (MTD) Sales are standard reports for most BI implementations. However, comparing year over year will skew your sales comparison because of where the weekends and holidays fall. For example, April 1, 2019, was a Monday. But April 1, 2018, was a Sunday. When we compare last year’s MTD to this year’s MTD it probably looks very good because we’ve had an extra business day for sales.

How do we do this without creating many complex measures in DAX? Expand our Date Dimension. The following are the steps to add Business Day reporting to Power BI.

1. Expand the Date Dimension

You will need to add some business day columns to your Date Dimension. If you don’t have a Date Dimension, go here to see how to create one in SQL Server. Add the following columns:

BusinessDay INT NULL,
BusinessDayKey INT NULL,
BusinessDayLastYearKey INT NULL,
BusinessDayLastMonthKey INT NULL,

2. Calculate the Business Day

Even businesses that primarily work on business days have occasional transactions on the weekend and holidays. Depending on your business requirements, you may assign weekend and holidays to the previous day or to the next business day unless it is the end of the month. If the month ends on a weekend or holiday, you still want to keep the transaction in the correct month and assign it to the last business day of the month.

Option 1 — Assign weekends to Friday

UPDATE D
SET BusinessDay = d2.BusinessDay
from Dim_Date d
join (select date, sum(case
                   when IsHoliday = 0 and IsWeekend = 0 
                   then 1
                   else 0
                   end) 
          over(partition by year(date), month(date)
          order by date
          ROWS UNBOUNDED PRECEDING) as BusinessDay
       from Dim_Date) d2
on d.Date = d2.Date

Option 2 — Assign Weekends to Monday

-- Move weekend and Holiday to next business day
-- End of month goes to last business day
UPDATE D
SET BusinessDay = CASE WHEN EXISTS(SELECT *      -- Check for any business days in the month after today
                  FROM Dim_Date AS D1
                  WHERE D1.[year] = D.[year]
                  AND D1.[month] = D.[month]
                  AND D1.[day] > D.[day]
                  AND (D1.IsWeekend = 0 and d1.IsHoliday = 0)   )
                  THEN (D.[day] -  -- Start with day of the month and subtract weekends and holidays
                 (SELECT COUNT(*)
                  FROM Dim_Date AS D1
                  WHERE (D1.IsWeekend = 1 or d1.IsHoliday = 1)
                  AND D1.[year] = D.[year]
                  AND D1.[month] = D.[month]
                  AND D1.[day] < D.[day])  )
                  ELSE (SELECT COUNT (*)     -- Return the count of business days in this month
                  FROM Dim_Date AS D1
                  WHERE D1.IsWeekend = 0
                    and d1.IsHoliday = 0
                    AND D1.[year] = D.[year]
                    AND D1.[month] = D.[month])
                  END
FROM Dim_Date AS D

3. Calculate the Business Day Key

update Dim_Date
set BusinessDayKey 
    = CASE WHEN BusinessDay > 0
           THEN DATEPART(yyyy,[date]) * 10000 + DATEPART(mm,[date]) * 100 + BusinessDay
           else null end,
    BusinessDayLastYearKey
    = CASE WHEN BusinessDay > 0
           THEN DATEPART(yyyy,dateadd(yy,-1,[date])) * 10000 + DATEPART(mm,[date]) * 100 + BusinessDay
           else null end,
    BusinessDayLastMonthKey
    = CASE WHEN BusinessDay > 0
           THEN DATEPART(yyyy,dateadd(mm,-1,[date])) * 10000 + DATEPART(mm,dateadd(mm,-1,[date])) * 100 + BusinessDay
           else null end

For this example, I went with option 2 (move weekends to Monday). A sample from April 2019 shows the weekend assigned to Monday, April 8.

4. Add the Business Day and Key to Your Sales or Transaction View Imported into Power BI

Select s.*
      ,d.BusinessDay
      ,d.BusinessDayKey
  FROM Sales s
  join Dim_Date d
    on s.[Transaction Date] = d.Date

5. Update Joins in the Model

First, you have to create a Role-Playing Dimension (Copy) of the sales table for last month and last year. For this example, we will only create last year. On the Modeling Tab, press New Table and copy your sales table, i.e. Sales Transaction Last Year = SalesTransaction

Join Dim_Date to Sales by BusinessDayKey to BusinessDayKey.

Join Dim_Date to Sales Last year by BusinessDayLastYearKey to BusinessDayKey.

You will get a many to many warning. Click OK to accept the join.

Edit the relationships to make the Cross filter direction = Single from the Dim_Date to the transaction tables.

It should look similar to this.

6. Add MTD and % Measures and Create the Report

You can use the Quick Measure wizard to create the MTD totals. Current and last year totals use the Date in Dim_Date. The join on the business key rolls up the correct business days.

Create the % change between the MTD columns.

7. Create the Report

We can create separate reports to verify the business day report.

Conclusion

The Date Dimension is critical for reporting. Take the time to understand your business requirements and expand the standard Date Dimension if needed. It will save you a lot of custom code and maintenance headaches in the future.