Calculate business days till today on per month basis

[Anonymous]

Hello there,

I am trying to calculate business days (and eventually business hours) per month, so I can use it in calculating machine efficiency. (Numer of hours machine has worked so far this month devided by total hours available).

I have Date Dimension table.

 And, this is the table from which I am calculating the total hours that a machine has worked.

Could you help me with this?

Thanks,

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Did you mean how many workdays are there in that month until the [FullDateAlternateKey]?

If yes,

workdays*hours =
VAR _hour_per_day = 8 //How many hours of work per day. 
VAR _Calendar =
    CALENDAR( DATE( [year], [month], 1 ), [FullDateAlternateKey] )
VAR _add =
    ADDCOLUMNS(
        _Calender,
        "workday", IF( WEEKDAY( [FullDateAlternateKey], 2 ) < 6, 1, 0 )
    )
RETURN
    COUNTROWS( FILTER( _add, [workday] = 1 ) ) * _hour_per_day

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak  Thanks for the reply. The solutoin is returning 0 and 1 only. 

[amitchandak]

@Anonymous , You can create a column like this in date table and use that as filter for business days

WorkDay= if( weekday([Date],2)<6 ,1,0)

refer

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

https://exceleratorbi.com.au/calculating-business-hours-using-dax/