Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I currently have a Date table populated with:
* FullDate (date)
* MonthBeginDate (date)
* MonthEndDate (date)
* IsBusinessDay (String "Y" or "N")
I am trying to add 3 columns in DAX (or in the query builder, but I doubt that is possible):
* BusinessDaysForMonth
* BusinessDaysPassedForMonth
* PercentBusinessDaysPassedForMonth
any help would be much appreciated
Solved! Go to Solution.
Hey,
thanks for taking the time to prepare the pbix file. I'm not able to recreate the calculated columns I posted in my initial answer - this is so weird.
Nevertheless - here you will find a working solution where the DAX statement of the calculated columns is slightly adjusted:
Business Days passed = var currentDate = 'Calendar'[Date] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var daterange = DATESBETWEEN('Calendar'[Date], firstdayofmonth, currentDate) return //countrows(daterange) CALCULATE( SUM('Calendar'[Is Business Day]) ,All('Calendar') ,daterange )
and
Business Days per Month = var currentDate = 'Calendar'[Date] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var lastdayofmonth = EOMONTH(currentDate, 0) var daterange = DATESBETWEEN('Calendar'[Date], firstdayofmonth, lastdayofmonth) return CALCULATE( SUM('Calendar'[Is Business Day]) ,All('Calendar') ,daterange )
Thanks for your patience and pointing me to an issue in my solution
Regards
Hey,
here are three calculated columns, please be aware that these calcualtions assume that a Business Day is flagged with 1 whereas a non business day with 0
The number of business days until the the current date
Business Days passed = var currentDate = 'Calendar'[Date] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var daterange = DATESBETWEEN('Calendar'[Date], firstdayofmonth, currentDate) return CALCULATE( SUM('Calendar'[Is Business Day]) ,daterange )
The number of Business Days in the Month
Business Days per Month = var currentDate = 'Calendar'[Date] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var lastdayofmonth = EOMONTH(currentDate, 0) var daterange = DATESBETWEEN('Calendar'[Date], firstdayofmonth, lastdayofmonth) return CALCULATE( SUM('Calendar'[Is Business Day]) ,daterange )
And the percentage of passed business days
Percent Business Days Passed For Month = 'Calendar'[Business Days passed] / 'Calendar'[Business Days per Month] * 100
Hope this helps
Regards
Thank you for the example. I added a whole value column based off of IsBusinessDay called BinaryBusinessDay, and then wrote the query below based on your example, starting with Business Days Passed:
Business Days Passed = var currentDate = 'Dates'[FullDate] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var daterange = DATESBETWEEN('Dates'[FullDate], firstdayofmonth, currentDate) return CALCULATE( SUM('Dates'[BinaryBusinessDay]) ,daterange )
However, it looks like this (any ideas?):
Hey,
I have to admit that it looks pretty much the same as in my example, that you can find here: pbix example
Have a look at the Report Page "Business Days", maybe you will see a difference.
Regards
After simplying your example, it would seem that the relationship between Calendar and Calendar Timeframe is somehow allowing it to work in your example. If I try to delete the table or relationship I get this:
If I try to recreate your example from scratch by copying just the advanced editor source of the Calendar table, I again get circular dependency errors when I start inserting the 'prevDate' and 'Business Days passed' calculation, but if I create the Calendar Timeframe table by copying the source DAX and duplicating the relationship, things start working.
Any idea what's going on with this?
Hey,
this is really odd. I created a new copy of my pbix file.
I deleted the tables Timefram, Calendar Timeframe (these table are not related to the "Business Day" question.
I deleted the calculated columns (from my Calendar table):
Until now - no Circular Dependencies
I recreated the calculated columns (in my Calendar table)
Without any issues!
Please give it another try. I assume you are using the latest Power BI Desktop release: 2.49.4831.521 64-bit (August 2017)
And can you please share a version of your pbix file
Regards
Thank you for your continued work with me, it is very appreciated. I'm currently running "Version: 2.49.4831.521 64-bit (August 2017)"
Upon further digging (with your new file) it would seem that things go south when I remove the FactWithDates relation.
This is what happens if it is the last relation removed:
And It goes back to the bunch of zeros and ones for the days passed when I leave just the FactWithDateIndex relation:
Any chance you could provide a working single table example?
Hey,
please provide a file with your calendar table.
Cheers
Here is about as barebones as I can go: https://www.dropbox.com/s/rirqkj41i2f8vrg/datetry.pbix?dl=1
Hey,
thanks for taking the time to prepare the pbix file. I'm not able to recreate the calculated columns I posted in my initial answer - this is so weird.
Nevertheless - here you will find a working solution where the DAX statement of the calculated columns is slightly adjusted:
Business Days passed = var currentDate = 'Calendar'[Date] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var daterange = DATESBETWEEN('Calendar'[Date], firstdayofmonth, currentDate) return //countrows(daterange) CALCULATE( SUM('Calendar'[Is Business Day]) ,All('Calendar') ,daterange )
and
Business Days per Month = var currentDate = 'Calendar'[Date] var firstdayofmonth = EOMONTH(currentDate, -1)+1 var lastdayofmonth = EOMONTH(currentDate, 0) var daterange = DATESBETWEEN('Calendar'[Date], firstdayofmonth, lastdayofmonth) return CALCULATE( SUM('Calendar'[Is Business Day]) ,All('Calendar') ,daterange )
Thanks for your patience and pointing me to an issue in my solution
Regards
Hi Tom, @TomMartens
I'm trying to implement your Business Days Passed Calc here, for the problem I posted: http://community.powerbi.com/t5/Desktop/Calculating-number-of-running-business-days-in-a-month-so-fa...
I have literally copied everything you've done, but when I put Business Days Passed in a bar chart I get crazy numbers that are over 1000. The Business Days Passed Column I have it's default summarization set to sum, in the value box for the graph it is also set to sum. This is what you set in the dropbox file in this post.
Is there some kind of nuance I'm missing here?
No worries on the delay, that worked great!
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |