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 @mark_endicott 

[mark_endicott · Accepted solution]

@wendereis - Please expand your screenshot to show what the error is. You have cut off the screenshot at where the error would show (although I can see it begins after the first variable). 

Here's a screenshot of mine to show the code works (note: I have set 27/06 and 28/06 as holidays to test):

[foodd]

Hello @wendereis , @mark_endicott , @rajendraongole1 , and @aduguid each have contributed guidance and offered solutions to your question.   Please consider the following when asking a question as this is the decorum when asking a question.

Provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[mark_endicott]

@wendereis - Your error looks to be because you are creating a measure, rather than a column.

The code I have given you will work in a column, but I want to make one change that will account for multiple consecutive holidays.

VAR CurrentDate = 'Table (8)'[Date]
VAR NumberOfDaysToSubtract = 3
VAR weekend_adjustment =
    SWITCH ( WEEKDAY ( 'Table (8)'[Date], 2 ), 1, 2, 2, 2, 3, 2, 0 )
VAR minus_weekends = ( CurrentDate - NumberOfDaysToSubtract ) - weekend_adjustment
VAR holiday_adjustment = CALCULATE( min('Table (8)'[Date] ), REMOVEFILTERS( 'Table (8)'[Date]),  'Table (9)'[holiday] > minus_weekends )
VAR calc =
    IF ( minus_weekends IN VALUES ( 'Table (9)'[holiday] ), holiday_adjustment, minus_weekends)
RETURN
    calc

This will now give you the next possible day after consecutive holidays. 

To make this work for -7 you can simply use the VAR adjustment from my previous measure. 

For -1 you can learn how the adjustment works and tailor it yourself. We are not here to do your work for you, but to help you learn. 

[wendereis]

"Your error looks to be because you are creating a measure, rather than a column".

Answer: No, I was inserting your formula in a "new column" (creating newcolumn). The error is still running.

Thank so much for trying help me and sorry for stopping you time to try solve it.

[mark_endicott · Accepted solution]

@wendereis - Please expand your screenshot to show what the error is. You have cut off the screenshot at where the error would show (although I can see it begins after the first variable). 

Here's a screenshot of mine to show the code works (note: I have set 27/06 and 28/06 as holidays to test):

[wendereis]

Hi my friend.
besides in my Power BI is appearing a message error after using this formula, as you show it works in your Power BI as print/picture you show, I am marking it as "accepted solution" for total help.

I'm trying to discover how to do your formula works for me without error, or another way to solve my problem.

Thank you so much!!

[wendereis]

Translated error message: "a circular dependency was detected Painel de Controle - Outbound[1_Teste2], 08daf9f0-f7fb-4f26-a951-77ce88ec6706, Painel de Controle - Outbound[1_Teste2].."

[mark_endicott]

@wendereis - this screenshot has not been expanded. I cannot see the error. Need to see something like this:

Note the error line at the bottom of the DAX window.

[wendereis]

Translated error message: "a circular dependency was detected Painel de Controle - Outbound[1_Teste2], 08daf9f0-f7fb-4f26-a951-77ce88ec6706, Painel de Controle - Outbound[1_Teste2].."

[mark_endicott]

@wendereis - Ok that's a strange error, but I promise you it's not being caused by my DAX. 

You have another column called 1_Teste2 - Can you please delete this, and see what it does to this error?

[wendereis · Accepted solution]

Hi!!

This Youtube Video Help me a lot:

https://www.youtube.com/watch?v=RD996KUYM8Q&list=PLzPjvUUXZT62h53QBORroNIyU8TyQILxM&index=5 

Tilte of Youtube video: 

"How to Add Business Days on Any Date in Power BI in Hindi | WORKDAY Alternative in Power BI"

[wendereis]

I don't have another column called "1_Teste2".

😞

Unfortunately it's still appearing this message for me.

besides my Power BI is appearing this message error after using this formula, as you show it works in your Power BI as print/picture you show, I am marking it as "accepted solution" for total help.

I'm trying to discover how to do your formula works for me without error, or another way to solve my problem.

Thank you so much!! 

[mark_endicott]

@wendereis - If I interpret your requirement correctly, something like this will work:

VAR CurrentDate = 'Table (8)'[Date]
VAR NumberOfDaysToSubtract = 7
VAR adjustment =
    SWITCH ( WEEKDAY ( 'Table (8)'[Date], 2 ), 1, 4, 2, 5, 3, 3, 4, 2, 5, 2 )
RETURN
    IF (
        OR (
            WEEKDAY ( CurrentDate, 2 ) IN { 6, 7 },
            LOOKUPVALUE ( 'Table (9)'[holiday], 'Table (9)'[holiday], CurrentDate )
        ),
        BLANK (),
        ( CurrentDate - NumberOfDaysToSubtract ) - adjustment
    )

If it does not, you need to supply more sample data and desired outputs. 

[wendereis]

Section 1:
Follow my comments:
The table and column in blue is date column I need to discount 3 days without  --> Saturday, Sunday and holiday dates  and the table and column in orange below is date of holiday table used in the formula in section 4 below and gave a wrong result.

----------------------------------------------------------------------------------------------------------------------
@Jihwan_Kim @xifeng_L @HotChilli @DataNinja @Greg_Deckler 
Section 2 (THIS IS WHAT I NEED, PLEASE):
Date Column                        (discount days in formula)                            Result Expected new Date Column            
 28/06/2024       -3 Days (without Saturday, Sunday and holiday date )                   = 25/06/2024

Case too with Saturday and Sunday:
Date Column                           (discount days in formula)                            Result Expected new Date Column    24/06/2024       -3 Days (without Saturday, Sunday and holiday when necessary )        = 19/06/2024

-----------------------------------------------------------------------------------------------------------------------
Section 3:
Note: I've applied your formula and my result is [wrong] 23/06/2024 instead 25/06/2024. 

(in this case just discount saturday and sunday. It can be in another cases that I will need to discount more 1 day besides Saturday and Munday if it be some holiday date).

------------------------------------------------------------------------------------------------------------
Section 4:
TO THE COMMENTS NOTES ABOVE I'VE USED YOUR FORMULA THIS WAY BELOW:

Column_Test (new column) =

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

VAR NumberOfDaysToSubtract = 3

VAR adjustment =

    SWITCH ( WEEKDAY ( 'Painel de Controle - Outbound'[1_2_DataPlan_FimSeparacao], 2 ), 1, 4, 2, 5, 3, 3, 4, 2, 5, 2 )

RETURN

    IF (

        OR (

            WEEKDAY ( CurrentDate, 2 ) IN { 6, 7 },

            LOOKUPVALUE ( 'Data feriados coluna única LL01'[Data feriados], 'Data feriados coluna única LL01'[Data feriados], CurrentDate )

        ),

        BLANK (),

        ( CurrentDate - NumberOfDaysToSubtract ) - adjustment

    )

[mark_endicott]

@wendereis - You started off by saying you need -7 days, now you're saying you want -3. This changes the adjustment you need to make, because 7 days will cross more weekends than 3. 

I also now understand what needs doing with holidays. The below should now work for you:

VAR CurrentDate = 'Table (8)'[Date]
VAR NumberOfDaysToSubtract = 3
VAR weekend_adjustment =
    SWITCH ( WEEKDAY ( 'Table (8)'[Date], 2 ), 1, 2, 2, 2, 3, 2, 0 )
VAR minus_weekends = ( CurrentDate - NumberOfDaysToSubtract ) - weekend_adjustment
VAR minus_holidays =
    IF ( minus_weekends IN VALUES ( 'Table (9)'[holiday] ), 1, 0 )
VAR holiday_adjustment = minus_weekends - minus_holidays
RETURN
    holiday_adjustment

If it works, please accept as the solution. 

[wendereis]

But I need to do it with 3 days too and in another column with 7 days and in another column 1 day of difference.

Note:
In my test yesterday with 7 days the formula discounted more than 7 days.

This new formula is with error in VAR when I try to use.

[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.