Number of Business Days

[rspears2001]

I have my source Days Out table as well as a Business Days table and a Holidays table...and they're alll working fine. The issue is when I filter the dashboard on the month, the number of business days doesn't change.

So I guess I need to figure out how to base the business days on the Days Out table while counting the number of business days available for that particular month selected.

Thoughts??

 

[Anonymous]

Hi,

Can you post a picture of your data model and your dax for Business Days?

Your interactions will be depenedant on your relationship and where your months for your graph visual is coming from.

[rspears2001]

The Days Out table is a stand alone table with no relationship, but here's the DAX for business days:

WorkDay = IF(WEEKDAY('Calendar'[Date],2)>5,0, IF('Calendar'[Date]=RELATED(tblHolidays[HolidayDate]),0,1))

[Anonymous]

Could you send a post  a copy of your PBiX?

There could be 101 reason why it doen't work, i have a feeling you either need a relationship in your model or you visual bar graph table is fed of one table and as your relationships don't exist it won't filter your business days.

[rspears2001]

The bar graph is based on the TicketID from tblDaysOut that is a stand alone table with no relationship, but here's the DAX for business days:

WorkDay = IF(WEEKDAY('Calendar'[Date],2)>5,0, IF('Calendar'[Date]=RELATED(tblHolidays[HolidayDate]),0,1))

I guess I'm looking for the best way to create a relationship between the tblDaysOut TicketDate column and the Calendar table Date column.

[v-juanli-msft]

Hi @rspears2001

From information above, the Business Days table is a calculated table created with the "date" column from Calendar table.

Please note that, since the Business Days table is a calculated table, it is static, it can't change with the slicer (using "date" column from Calendar table).

Also, as I known, it is not supported to create relationships between the two tables based on the "date" column, for the calculated table is created by this column.

I would suggest you to create a measure for "WorkDay", thus, it can change with the slicer.

Best Regards

Maggie

[rspears2001]

Hi @v-juanli-msft thanks for the response. I thought about just adding the workday measure to my table but how would I account for or capture work days that there is no activity? 

Here's a handful of my KPI's:

- # of activities in a given period

- # of work days in that period

- # of work days with activity in that period

- % of work days that had activity in that period

Thanks again,

Rodney

[v-juanli-msft]

Hi @rspears2001

Does the measure "Workdays" work for you when you add it to the kpi?

You want to calculate work days that there is no activity, right?

And the four items listed are all you want to calculate with measures, right?

However, without some example data, I can't figure out these measure.

Best Regards

Maggie

[rspears2001]

Here's a really basic test version,. but I think you'll get the gist:

https://www.dropbox.com/s/r01di46zlwqdrdi/Work_Days_Test.pbix?dl=0