Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DBAngst
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
BIcurate
New Member

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 TableDates TablePower BI ReportPower BI ReportSales TableSales 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

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

9 REPLIES 9
BIcurate
New Member

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 TableDates TablePower BI ReportPower BI ReportSales TableSales 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...

Hello! How did you create the Working Day column? I am trying to find a way to identify the last 20 business dates of the month and I think this Working Day might help my issue. 

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])
    )
)
amitchandak
Super User
Super User

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.

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?

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 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
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.