Forum Discussion

elycape90's avatar
elycape90
Regular Visitor
2 years ago
Solved

Difference date calculation considering holidays per counties

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

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Dangar332
    2 years ago

    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

4 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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's avatar
      elycape90
      Regular Visitor
      2 years ago

      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's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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