Calculate dynamically No. of business days passed in a month.

[Anonymous]

Hi,

I have a table which already have Date and Revenue but I would like to create a column No. of working days passed (Column c) and then divide Column B by Column C to get Column D. But i am not able to find  away to dynamically calculate no. of business days passed. This is excluding weekends and holidays(4 JULY).

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create calendar table first, assuming you have created the Holiday table via Enter Data, then create relationships among the calendar table , Data table and Holiday table on date field, keep Cross filter direction as Both.

Table: DateDim = CALENDAR(MIN(Data[DATE]),MAX(Data[DATE]))

Secondly, create column and measure in Data table like DAX below.

Column: 

IsWorkDay = IF (WEEKDAY(MAX(DateDim[Date]),2)<=5 && COUNTX(RELATEDTABLE(Holiday),1)<1,1,0)



Measure: 

No. of working days passed = IF(MAX(Data[IsWorkDay])<>0,CALCULATE(COUNTX(FILTER(ALLSELECTED(Data),Data[IsWorkDay]<>0),Data[IsWorkDay]),FILTER(ALLSELECTED(Data),Data[OrderDate]<=MAX(Data[OrderDate]))),BLANK())

Best Regards,

Amy

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