Need Help Writing DAX for "Count Business Days in the Weeks Selected in the Filter"

[Anonymous]

I need help with the formula for "Avg New" which are new tickets in the period. I do not know how to write the denominator formula. Right now, I hard coded it to say 35 (which is wrong anyway). 

Basically, I need the denominator to count the # of business days (Mon-Fri) in the weeks selected in the filter. 

FYI - For the weeks shown on the filter, I have those listed in the main *Smartsheet* table with a week end on Sat. I do have a date table but I do not know how to add a "week ending on Sat" column to my date table so not sure if I need that or not. Below is my date table query.

Date Email Received =

VAR __startDate = DATE ( 2022, 1, 1 )

VAR __endDate = DATE ( YEAR ( TODAY() ), 12, 31 )

VAR __dates = CALENDAR ( __startDate, __endDate )

RETURN

ADDCOLUMNS (

__dates,

"YYYY-MM-DD", FORMAT ( [Date], "YYYY-MM-DD" ),

"Year", YEAR ( [Date] ),

"Month Number", MONTH ( [Date] ),

"Month Full Name", FORMAT ( [Date], "MMMM" ),

"Month Short", FORMAT( [Date], "MMM"),

"Month, YYYY", FORMAT( [Date], "MMM, YYYY" ),

"Month YYYY-MM", FORMAT( [Date], "YYYY-MM" ),

"Week", FORMAT (WEEKNUM([Date], 1 ),"00"),

"YYYY-Week", concatenate(CONCATENATE(FORMAT ( [Date], "YYYY" ),"-"), FORMAT (WEEKNUM([Date], 1 ),"00")),

"Quarter", "Q" & FORMAT( [Date], "Q, YYYY" ),

"Quarter Sort", FORMAT ( [Date], "YYYY-Q" ) ,

"Day Name", FORMAT ( [Date], "DDD" ) ,

"Day Number", FORMAT ( [Date], "DD" ) ,

"Day Full Name", FORMAT ( [Date], "DDDD" )

)

[Anonymous]

Thanks for your help. I was able to find another way to do it. I just don't know enough about DAX and formulas but I found a way to rig up the calculation I need. 

[v-shex-msft]

Hi @Anonymous,

Did you mind sharing these here? I think they will be help for others who has a similar requirements.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

I added a "Business Day" column to my date table as follows:

Then I built a formula that sums the number of business days for the denominator

[Greg_Deckler]

@Anonymous Use NETWORKDAYS

[Anonymous]

Thanks @Greg_Deckler  but I am still not sure how to write the DAX code. This doesn't work

[Greg_Deckler]

@Anonymous NETWORKDAYS is a function, / NETWORKDAYS( [start date], [end date]) for example.

NETWORKDAYS function (DAX) - DAX | Microsoft Learn

Are you on an old version of the Desktop?

If so, you can do it the old fashioned way: Net Work Days - Microsoft Power BI Community