Percentage of Business Days passed for the month

[Tuckie]

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

[TomMartens]

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

[Tuckie]

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

[TomMartens]

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

[Tuckie]

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?

[TomMartens]

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

• Percent Business Days Passed For Month
• Business Days per Month
• Business Days passed

Until now - no Circular Dependencies

I recreated the calculated columns (in my Calendar table)

• Business Days passed
• Business Days per Month
• Percent Business Days Passed For Month

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

[Tuckie]

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?

[TomMartens]

Hey,

please provide a file with your calendar table.

Cheers

[Tuckie]

Here is about as barebones as I can go: https://www.dropbox.com/s/rirqkj41i2f8vrg/datetry.pbix?dl=1

[TomMartens · Accepted 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

[Anonymous]

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? 

[Tuckie]

No worries on the delay, that worked great!