How to add # of days in a date excluding weekends per country

[Anonymous]

Hi, I am looking for answers but can't find it. I need to add # of days to a date. However, per country there are specific numbers. Example:
Australia - 31 days
India - 10 days, so on. These two countries should exclude weekend when adding date, for other countries no need to exclude. Thank you in advance!

[v-yanjiang-msft]

Hi @Anonymous ,

Sorry I'm not very clear about your desired output.

Do you want to count the days from beginning of the year exclude weekends, like this?

Then what did you mean "Australia - 31 days, India - 10 days", for these two countries, can you show the expected result in Excel like me?

Best Regards,
Community Support Team _ kalyj

[Anonymous]

Hi, please see this example. I have a country column and query receieved dat. Per country they have an allowable due date, it will depend on their allowable due date.

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution.

Create a measure.

Expected date =
VAR _Day =
    SWITCH ( MAX ( 'Table'[Country] ), "Australia", 31, "India", 10, 2 )
RETURN
    MAX ( 'Table'[Date] ) + _Day

 Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi! So far, I'm getting the result. However, I need to exclude weekends on calculation for Australia and India.

[v-yanjiang-msft]

Hi @Anonymous ,

do you mean for Australia, when the date 1/1/2022 +31, the 31 days should exclude weekends?

Best Regards,
Community Support Team _ kalyj

[Anonymous]

Yes, weekends should be included. There are other countries that can include weekends when counting.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

1. Create a new table.

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
    "Week", WEEKDAY ( [Date], 2 )
)

2.Create a new measure.

Expected date =
VAR _T =
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date]
            > MAXX (
                FILTER ( 'Table', 'Table'[Country] = MAX ( 'Table'[Country] ) ),
                'Table'[Date]
            )
            && 'Date'[Week] <> 6
            && 'Date'[Week] <> 7
    )
VAR _T2 =
    ADDCOLUMNS ( _T, "Rank", RANKX ( _T, [Date],, ASC, DENSE ) )
RETURN
    SWITCH (
        MAX ( 'Table'[Country] ),
        "Australia", MAXX ( FILTER ( _T2, [Rank] = 31 ), [Date] ),
        "India", MAXX ( FILTER ( _T2, [Rank] = 10 ), [Date] ),
        MAX ( 'Table'[Date] ) + 2
    )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hello!!! THanks for this solution! It saved me! Appreciate it a lot.

[mh2587]

# of Days = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar',[Date]>=MAX('Table'[StartDate])&&[Date]<=MAX('Table'[EndDate])&&[Weekday]<>6&&[Weekday]<>7))