topic Re: Discount Days of one Specific Date Column with Networkdays in DAX Commands and Tips

Discount Days of one Specific Date Column with Networkdays

wendereis — Thu, 04 Jul 2024 14:57:29 GMT

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

Re: Discount Days of one Specific Date Column with Networkdays

aduguid — Mon, 01 Jul 2024 14:42:42 GMT

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 ) )

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Mon, 01 Jul 2024 18:11:36 GMT

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

Re: Discount Days of one Specific Date Column with Networkdays

rajendraongole1 — Tue, 02 Jul 2024 06:17:38 GMT

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!!

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Tue, 02 Jul 2024 12:25:40 GMT

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]

)

Re: Discount Days of one Specific Date Column with Networkdays

mark_endicott — Thu, 04 Jul 2024 17:46:05 GMT

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.

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Fri, 05 Jul 2024 13:44:11 GMT

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

)

Re: Discount Days of one Specific Date Column with Networkdays

mark_endicott — Fri, 05 Jul 2024 11:49:35 GMT

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.

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Fri, 05 Jul 2024 12:15:00 GMT

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.

Re: Discount Days of one Specific Date Column with Networkdays

mark_endicott — Fri, 05 Jul 2024 13:50:38 GMT

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.

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Fri, 05 Jul 2024 14:19:08 GMT

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

Re: Discount Days of one Specific Date Column with Networkdays

mark_endicott — Fri, 05 Jul 2024 14:33:57 GMT

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):

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Fri, 05 Jul 2024 15:01:36 GMT

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].."

Re: Discount Days of one Specific Date Column with Networkdays

mark_endicott — Fri, 05 Jul 2024 14:50:39 GMT

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.

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Fri, 05 Jul 2024 15:23:07 GMT

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].."

Re: Discount Days of one Specific Date Column with Networkdays

mark_endicott — Fri, 05 Jul 2024 15:46:50 GMT

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?

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Sun, 07 Jul 2024 14:31:07 GMT

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!!

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Sun, 07 Jul 2024 14:30:36 GMT

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!!

Re: Discount Days of one Specific Date Column with Networkdays

foodd — Sun, 07 Jul 2024 14:35:41 GMT

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-Forum/ba-p/963216

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/1447523/highlight/true#M607150

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

Re: Discount Days of one Specific Date Column with Networkdays

wendereis — Tue, 09 Jul 2024 14:14:28 GMT

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"