cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DBAngst Frequent Visitor
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
BIcurate Frequent Visitor
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.PNGDates TablePBIX.PNGPower BI ReportSales.PNGSales 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...

View solution in original post

DBAngst Frequent Visitor
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])
    )
)

View solution in original post

8 REPLIES 8
amitchandak Super Contributor
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.

DBAngst Frequent Visitor
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?
Highlighted

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 ),
Calendar[BusinessDay] = __currentBusinessDay
)





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.





amitchandak Super Contributor
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

DBAngst Frequent Visitor
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.

BIcurate Frequent Visitor
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.PNGDates TablePBIX.PNGPower BI ReportSales.PNGSales 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...

View solution in original post

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.





DBAngst Frequent Visitor
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])
    )
)

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,543)