Difference date calculation considering holidays per counties

[elycape90]

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

[v-yangliu-msft · Accepted solution]

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

[v-yangliu-msft · Accepted solution]

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

[Dangar332]

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)

[elycape90]

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 

[Dangar332 · Accepted solution]

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 @v-yangliu-msft 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