Discount Days of one Specific Date Column with Networkdays

[wendereis]

Hi guys!

Please, I need this answer a lot.

I have a Power BI Column "Date" and I need discount 7 days from this specific date take off satuday, sunday and others holidays dates in a Holiday table.

Example:
     Date Column             (discount days in formula)        Result in a new Date Column               
     26/06/2024                 -7 Days (networkdays)                   = 16/06/2024

Is it possible?

@rajendraongole1  @Idrissshatila @amitchandak 

[rajendraongole1]

Hi @wendereis - Create a new column in Power BI that discounts 7 working days (excluding Saturdays, Sundays, and holidays) from a specific date. 

Hope you have holiday date so, i am using not in values of holiday date table.

Discounted Date =
VAR CurrentDate = 'YourTable'[Date]
VAR NumberOfDaysToSubtract = 7
VAR AllDates = CALENDAR(MIN('YourTable'[Date]), MAX('YourTable'[Date]))
VAR WorkingDates =
FILTER (
AllDates,
NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) && -- Exclude Saturday (6) and Sunday (7)
NOT ( [Date] IN VALUES ( 'Holidays'[HolidayDate] ) ) -- Exclude holidays
)
RETURN
MAXX (
TOPN (
NumberOfDaysToSubtract + 1,
FILTER (
WorkingDates,
[Date] <= CurrentDate
),
[Date],
DESC
),
[Date]
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[wendereis]

Hi @rajendraongole1 

I've applied your formula, but the result didn't discount the  "-7 days". Return the same "date" without discount.

The colorfull information in blue below is my column base, and the information in green is my holidays table that contain just one column with holidays dates.

Can you see if there is any error in the formula, please?

FORMULA:

Discounted Date =

VAR CurrentDate = 'Painel de Controle - Outbound'[1_1_DataPlan_InicioSeparacao]

VAR NumberOfDaysToSubtract = 7

VAR AllDates = CALENDAR(MIN('Painel de Controle - Outbound'[1_1_DataPlan_InicioSeparacao]), MAX('Painel de Controle - Outbound'[1_1_DataPlan_InicioSeparacao]))

VAR WorkingDates =

FILTER (

AllDates,

NOT ( WEEKDAY ( [1_1_DataPlan_InicioSeparacao], 2 ) IN { 6, 7 } ) && -- Exclude Saturday (6) and Sunday (7)

NOT ( [1_1_DataPlan_InicioSeparacao] IN VALUES ( 'Data feriados coluna única LL01' ) ) -- Exclude holidays

)

RETURN

MAXX (

TOPN (

NumberOfDaysToSubtract + 1,

FILTER (

WorkingDates,

[1_1_DataPlan_InicioSeparacao] <= CurrentDate

),

[1_1_DataPlan_InicioSeparacao],

DESC

),

[1_1_DataPlan_InicioSeparacao]

)

[aduguid]

Try creating a calculated column.

Result Date = 
VAR StartDate = 'YourTable'[Date]
VAR Holidays = SELECTCOLUMNS(HolidayTable, "Date", HolidayTable[HolidayDate])
RETURN 
    CALCULATE(
        MAXX(
            ADDCOLUMNS(
                GENERATESERIES(1, 100),
                "Workday", 
                IF(
                    WEEKDAY(DATEADD(StartDate, -[Value], DAY), 2) <= 5 && 
                    NOT(DATEADD(StartDate, -[Value], DAY) IN Holidays), 
                    DATEADD(StartDate, -[Value], DAY), 
                    BLANK()
                )
            ), [Workday]
        ),
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(1, 100),
                "Workday", 
                IF(
                    WEEKDAY(DATEADD(StartDate, -[Value], DAY), 2) <= 5 && 
                    NOT(DATEADD(StartDate, -[Value], DAY) IN Holidays), 
                    DATEADD(StartDate, -[Value], DAY), 
                    BLANK()
                )
            ), 
            [Workday] <> BLANK() && RANKX(
                ADDCOLUMNS(
                    GENERATESERIES(1, 100),
                    "Workday", 
                    IF(
                        WEEKDAY(DATEADD(StartDate, -[Value], DAY), 2) <= 5 && 
                        NOT(DATEADD(StartDate, -[Value], DAY) IN Holidays), 
                        DATEADD(StartDate, -[Value], DAY), 
                        BLANK()
                    )
                ), 
                [Workday]
            ) = 7
        )
    )

[wendereis]

It wasn't work. It's very complex.