cancel
Showing results for
Did you mean:
Frequent Visitor

## Same Business Day from the Same Month of the Previous Year

Hello,

I have a Date Dimension table with a column named [BusinessDay] that counts the business days for each month by excluding weekends and holidays. If it is a weekend or holiday then this column is blank, otherwise it has a whole number between 1 and 23 for a each month. This column comes from the Data Warehouse and is not a Calculated column. I also have a flag for Business Days named [isBusinessDay] that has a 1 for true and 0 for false.

I am building a Business Day Sales Report with a table visual that has a row for each business day of the current month, and I need to calculate total sales for the current Business Day, and total sales for the same business day of the same month in the previous year. That is what I can't figure out.

Example: If today is the 10th Business Day of September 2019, then I need to be able to sum the sales from the 10th Business Day of September 2018 to make a meaningful comparison.

If I use DATEADD to look at the same day last year, it may be a weekend or holiday. Other time intelligence functions have similar limitations. From what I've read online, I think this needs to be written out using CALCULATE, FILTER, and VALUES, but I can't figure it out.

Can anyone help with this? Please let me know if you need more detail on my model or screenshots of anyting.

Thanks for your time!

Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Frequent Visitor

## Re: Same Business Day from the Same Month of the Previous Year

Hi

You require a custom DAX measure to do this.

See images of an Excel file with a dummy date table (using South African Public Holidays to calculate working days), and a dummy Sales table. I have created a simple SUM(Sales) measure and then the measure you're looking for.

Dates TablePower BI ReportSales Table

The measure is as follows:

```Total Sales PY Equivalent Business Day =
VAR v_BusinessDay = MAX(Dates[Working Day])
VAR v_Month = MAX(Dates[Month])
VAR v_PreviousYear = MAX(Dates[Year]) - 1
RETURN IF( v_BusinessDay =0 ; BLANK() ; CALCULATE([Total Sales]; FILTER( ALL(Dates); Dates[Month] = v_Month && Dates[Working Day] = v_BusinessDay && Dates[Year] = v_PreviousYear )))
```

The 3 variables just work out what is selected in the filter context (and the previous year). The IF just checks if the day selected is actually a working day, and if so returns the equivalent data from the previous year. If it's not a working day, it returns BLANK(). you can modify this behaviour as you require.

I'll upload the files to my blog and put a link here shortly...

Frequent Visitor

## Re: Same Business Day from the Same Month of the Previous Year

Hi @BI ,

Thanks for your help, your solution worked! Also, after posting this and asking for help I have since found another way to do this without variables. See below.

```BusinessDay Sales Prior = CALCULATE (
SUM ( 'Sales'[Amount] ),
FILTER (
ALL ( 'Date Dimension' ),
'Date Dimension'[BusinessDay] = MAX ( 'Date Dimension'[BusinessDay] )
&&  'Date Dimension'[Year] = MAX( 'Date Dimension'[Year] ) - 1
&&  'Date Dimension'[Month] = MAX( 'Date Dimension'[Month])
)
)```
8 REPLIES 8
Highlighted
Super Contributor

## Re: Same Business Day from the Same Month of the Previous Year

While the same business day can be calculated in formula. I suggest you should first calculate the business day of the month as a  column.

Like the sum of business days from the start date of the month.

Then any of your formula, you can compare month, year and business day.

Frequent Visitor

## Re: Same Business Day from the Same Month of the Previous Year

Hi, thanks for the reply. I believe the column you suggest I create is the column from my Date Dimension that I described at the beginning of my post. The one named [BusinessDay]? If so, how would you write this out in DAX?
Super User

## Re: Same Business Day from the Same Month of the Previous Year

@DBAngst try following measure

```Same Business Day Sales PY =
VAR __currentBusinessDay = MAX ( Calendar[BusinessDay] )
RETURN
CALCULATE (
SUM ( Table[Sales] ),
DATEADD ( Calendar[Date], -1, YEAR ),
)```

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super Contributor

## Re: Same Business Day from the Same Month of the Previous Year

Please find the file.

There is a measure which tells how many workdays (Business days) of that month. You also need to consider the workday flag. Using these two and year you should able to create a formula that gives you same business day last year

Frequent Visitor

## Re: Same Business Day from the Same Month of the Previous Year

Hi @pa Thanks for your help. I tested your Measure and it does not work unfortunately, everything shows up blank.

Frequent Visitor

## Re: Same Business Day from the Same Month of the Previous Year

Hi

You require a custom DAX measure to do this.

See images of an Excel file with a dummy date table (using South African Public Holidays to calculate working days), and a dummy Sales table. I have created a simple SUM(Sales) measure and then the measure you're looking for.

Dates TablePower BI ReportSales Table

The measure is as follows:

```Total Sales PY Equivalent Business Day =
VAR v_BusinessDay = MAX(Dates[Working Day])
VAR v_Month = MAX(Dates[Month])
VAR v_PreviousYear = MAX(Dates[Year]) - 1
RETURN IF( v_BusinessDay =0 ; BLANK() ; CALCULATE([Total Sales]; FILTER( ALL(Dates); Dates[Month] = v_Month && Dates[Working Day] = v_BusinessDay && Dates[Year] = v_PreviousYear )))
```

The 3 variables just work out what is selected in the filter context (and the previous year). The IF just checks if the day selected is actually a working day, and if so returns the equivalent data from the previous year. If it's not a working day, it returns BLANK(). you can modify this behaviour as you require.

I'll upload the files to my blog and put a link here shortly...

Super User

## Re: Same Business Day from the Same Month of the Previous Year

@DBAngst not sure why it didn't worked, it should. Do you have sample data to share in pbix to look into it. Although there are other solutions which you can test but I expect the measure I provided should work.

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Same Business Day from the Same Month of the Previous Year

Hi @BI ,

Thanks for your help, your solution worked! Also, after posting this and asking for help I have since found another way to do this without variables. See below.

```BusinessDay Sales Prior = CALCULATE (
SUM ( 'Sales'[Amount] ),
FILTER (
ALL ( 'Date Dimension' ),
'Date Dimension'[BusinessDay] = MAX ( 'Date Dimension'[BusinessDay] )
&&  'Date Dimension'[Year] = MAX( 'Date Dimension'[Year] ) - 1
&&  'Date Dimension'[Month] = MAX( 'Date Dimension'[Month])
)
)```

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)