topic Difference date calculation considering holidays per counties in DAX Commands and Tips

Difference date calculation considering holidays per counties

elycape90 — Wed, 27 Mar 2024 11:17:02 GMT

Hi all,

I need to calculate networkingdays between 2 dates considerinh weekend and country holidays.

In excell I succeded by creating a table with holidays per country (pic attached) and by applying the following formula:

IF(D3>0;NETWORKDAYS.INTL(C3;D3;1;FILTER(Holiday;Holiday[#Headers]='base dati'!G3))-1;"n/a")

D3 is the date of delivery C3 is the date of the entrust of the delivery.

Holiday is the table below

I want to do the same in Power Bi.

I use this formula to calculate the difference, but I dont'know how to apply the excel FILTER formula

IF(Sheet1[Data consegna]=BLANK(),BLANK(),IF(Sheet1[Data consegna]>0, NETWORKDAYS(Sheet1[Data Spedizione], Sheet1[Data consegna],1)-1))

I have a sheet with the database

I have a sheet with the holidays per each European country calledPubHol

Thanks in advance for your help

Re: Difference date calculation considering holidays per counties

Dangar332 — Wed, 27 Mar 2024 14:55:48 GMT

hi, elycape90

if you want to consider weekends and holidays in working days then simply use datediff()
it give you difference b/w two date

measure= datediff(Sheet1[Data Spedizione], Sheet1[Data consegna],day)

Re: Difference date calculation considering holidays per counties

elycape90 — Wed, 27 Mar 2024 21:31:58 GMT

Hi! But I need to consider the right holiday for each country. So how can I make it ? For ecample the first riw is related to a delivery in France which has got some holidays and the second row is related to Spain which has got other holidays

Re: Difference date calculation considering holidays per counties

Anonymous — Thu, 28 Mar 2024 06:39:29 GMT

Hi elycape90 ,

Thank you Dangar332 for your replies, I have the following references ideas based on the more detailed information provided by elycape90 .

Here are the steps you can follow：

1. Create calculated table.

Date = CALENDAR( DATE(2023,1,1),DATE(2023,12,31))

2. Create calculated column.

Days = var _select=SELECTCOLUMNS(FILTER(ALL('PubHol'),'PubHol'[country]=EARLIER('Table'[country])),"date",'PubHol'[Festa]) return COUNTX( FILTER(ALL('Date'), 'Table'[country]=EARLIER('Table'[country])&& 'Date'[Date]>=EARLIER('Table'[Date Spedizione])&&'Date'[Date]<=EARLIER('Table'[Data consegna])&& NOT('Date'[Date]) in _select),'Date'[Date])

3. Result:

Best Regards,

Liu Yang

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

Re: Difference date calculation considering holidays per counties

Dangar332 — Thu, 28 Mar 2024 12:57:03 GMT

hi, elycape90

as i understand you want to count days b/w two dates with not include of weekends and holidays for that particular country
if i am right then try below code with Anonymous data

Column 2 = var a = 'Table'[country] var b = CALCULATETABLE(VALUES(PubHol[Festa]),PubHol[country]=a) RETURN NETWORKDAYS('Table'[Date Spedizione],'Table'[Data consegna],1,b)

refer below file for solution
column2 is my code in Table

if still you not get your result then please clarify your requirment

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