Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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.
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...
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]) ) )
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.
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]) ) )
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |