Is workingday column calculation in date dimension with Holidays range

[Surendra_thota]

i have implemented isworking day column with the refrence of one datecolumn in a  holidays refernece

IsWorkingDay = IF (NOT(DateTime[Day of Week]= "Saturday" || (DateTime[Day of Week]= "Sunday")) && COUNTX(RELATEDTABLE(Holidays),Date)<1,1,0)

Now the  Holidays refrence table splited  into holidaystartdate and HolidayEnddate.

How to handle this

[v-sihou-msft · Accepted solution]

@Surendra_thota

Based on your description, now you have a holiday reference table which specifies a data range for each holiday. You want to add a isworkingday column in your date diemnsion table. Right?

I assume your Holiday table like below, and you build the relationship between two tables already:

You can apply the condition to check number of records in Holiday table where the current date is within the date range. Please create a calculated column below;

isworkingday = IF(WEEKDAY('Table'[Date],2)=6 || WEEKDAY('Table'[Date],2)=7 || 
	CALCULATE(COUNTROWS(Holiday),FILTER(Holiday,Holiday[start date]<='Table'[Date] && Holiday[end date]>='Table'[Date]))>0
	,0,1 )

Regards,

[Surendra_thota]

@v-sihou-msft

Thank you very much for the solution , we have used the same DAX  what you have mentioned in our trails , but the dotted relationship between date and endate is not created , so we are able get for date related to startdate ,  we dont aware that we can give relation twice  between the same tables.

Thanks for  this .

[zornstv]

I'm using this syntax to calculate working days.   How do I handle null values when either the beginning or ending date is missing.

Production TAT =
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( 'Order'[date_ProductionScheduled] )
            && 'Calendar'[Date] <= MAX ( 'Order'[date_ProductionDone] )
    ),
    'Calendar'[IsWorkingDay]
)

[BhaveshPatel]

You should check this community thread:

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat...

Got some intersting stuff that can lead you to the final results.