Network Days betweek two dates calculation only excluding public holiday

[tauseefau]

HI Team

i am wondering if anyone can help me . I am trying to caclulate number of days beweet two days

date A relates to creation of a ticket and Date B relates to reolution of ticket . what i also need to exclude is the Public holiday between these two days .

'Australian Public Holiday' is another table which has list of All the public holidays and has column which is Holiday count as 1

what i have done is use calender function

below creation of columns function

Var TBL_Date = CALENDAR(Fault_Data[Created Date Only],IF(ISBLANK(Fault_Data[Resolved Date Only])=TRUE(),TODAY(),Fault_Data[Resolved Date Only]))

Var TBL_FinalDate =

ADDCOLUMNS(TBL_Date,

"Holiday",IFERROR(LOOKUPVALUE('Australian Public Holiday'[Holiday Count],'Australian Public Holiday'[Date],[Date]),0)

)

var NetworkDays = SUMX(TBL_FinalDate,IF([Holiday]=1,0,1))

when i check the network days for example between 10 April 2020 to 17th April 2020 it does not exclude

the public holidays . which are two 10th and 13th and should return 15 days not 17 but rather its returning

17 days .

Any help would be greatly appreciated

Thanks and Regards

[Anonymous]

Column_ = // calc column
var __startDate = Fault_Data[Created Date Only]
var __endDate = 
coalesce(
    Fault_Data[Resolved Date Only],
    today()
)
var __result =
if( __startDate <= __endDate,
		
    var __totalDaysWithHols =
        DATEDIFF(__startDate, __endDate, DAY) + 1
    var __holsCount =
        COUNTROWS(
            FILTER(
                'Australian Public Holiday',
                __startDate <= 'Australian Public Holiday'[Date]
                &&
                'Australian Public Holiday'[Date] <= __endDate
            )
        )
    return
        __totalDaysWithHols - __holsCount + 0
)
RETURN
    __result

[tauseefau]

HI ,

Thanks for solution ,  still cant get through result . ie see below there is public holiday on 12 and 13th april

i am getting result in minus if you see in screen shot ticket created on friday 10th April and resolved in 27th April

please let me know if you have any further questions

Thanks and Regards

tauseef

[Anonymous]

Well, the code is perfectly correct. If you get an unexpected result, it may be because not all the constraints that the code calls for are satisfied. Troubleshooting is easy. Just return the constituent parts that the code is built from and check if they're correct: __totalDaysWithHols, __holsCount. If these two are always correct, then the output will always be correct. If not, then you'll know where you should change what.

[harshnathani]

Hi @tauseefau ,

See if this helps.

https://community.powerbi.com/t5/Desktop/Calculate-working-days-between-dates-excluding-holidays/m-p...

https://community.powerbi.com/t5/Desktop/How-exclude-holidays-from-networking-days/m-p/785656

Regards,

Harsh Nathani

[amitchandak]

@tauseefau , See if this file can help. Check 2nd page

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0