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 app...
  • 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

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident 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
    Icon for Resident Rockstar rankResident 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