Adding working days

[Anonymous]

Hello,

I am trying to add working days to a date if certain conditions are respected to create a target date. I have tried many things from the internet but nothing works and i'm more lost now than I was before. 

I created a new request with a list of dates from 2017 to 2023, I created a column that writes 1 if it's a working day and 0 if it's not (Jours ouvrés). (I downloaded an excel file with the holydays)

That request works.

Now I have another query where I have the Date column I want to add 7 working days to, if certain conditions are met: IF(NOT(ISBLANK('Tracking'[RELANCE 1])) && Tracking[CONFORMITE]="FAUX", IF( ISBLANK('Tracking'[Document reçu]) || ISBLANK(Tracking[Date réception document]),

then add 7 days to 'Tracking'[RELANCE 1], else BLANK()),BLANK())

I have tried a few things like creating these columns (https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-date/m-p/146945)  : Rank1 = RANKX(FILTER(Calendrier,Calendrier[Jours ouvrés]=1),Calendrier[Dates],,ASC)
+ 10 jours ouvrés = LOOKUPVALUE(Calendrier[Dates],Calendrier[Jours ouvrés],1,Calendrier[Rank1],Calendrier[Rank1]+10)
+ 7 jours ouvrés 

But I'm stuck when I have to relate the Calendrier query and my other query (where I have the column I want to add working day).

I wanted to create a relationship between them, which I did but I could only choose multiple to multiple and when I use the RELATE function like in the link it doesn't find the table.

Can anyone help me?

[rohit_singh]

Hello @Anonymous ,

First of all please ensure that you're using the inbuilt date functionalities in Power BI to compute working days, rather than using an external lookup file. 
In Power Query, add the following column to your date table (calendrier)

Assuming Monday as the first day of the week, it is assgined a value of 0. Hence, Saturday and Sunday have values 5 and 6 respectively.
You now get the column Jours ouvrés without using an external lookup.

I have assumed hat your Tracking table looks like below

In the report view, create a 1:M relationship between your date table and the Tracking table

Once this relationship has been created, go to your Tracking table and add a new column based on the relationship created above. This will give you the weekday flag as a column on the Tracking table

Jours ouvrés = RELATED(dim_date[Jours ouvrés])

Next, based on your requirement add a new column. To add 7 days to the given date use 

DATEADD(Tracking[RELANCE 1], 7 ,DAY)

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Thank you so much.

It's not working though, it says The "DATEADD" function expects a contiguous selection when the date column is not unique, is discontinuous, or contains a time part.

[rohit_singh]

Hi @Anonymous ,

Please ensure that your date columnis formatted as date and not date/time.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  😊

[Anonymous]

Hello @rohit_singh,

I Checked and the column is in Date and not Date time but It may be because the column of dates is discontinued (sometimes there are dates, sometimes it's empty).

I did it many times in different ways, I don't always get the message but in any case it doesn't add the days, the column where it's supposed to be +7 days is completly empty.

[v-jingzhang]

Hi @Anonymous 

I create a sample demo attached at bottom. See if it helps. 

First create a new column in the calendar table to get the +7 working date for every date. 

+ 7 Working Days = 
MAXX (
    TOPN (
        7,
        FILTER (
            'Calendar',
            'Calendar'[Date] > EARLIER ( 'Calendar'[Date] )
                && 'Calendar'[IsWorkingDay] = 1
        ),
        'Calendar'[Date], ASC
    ),
    'Calendar'[Date]
)

Then in 'Tracking' table, get the +7 working date with RELATED function. 

The relationship between two tables as below. 

If 'RELANCE 1' value is blank, it returns blank too. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hello @v-jingzhang ,

Thank you so much! It works!!! Now the only problem I have is that I want to that with 2 different columns in the same query: Relance 2 and Relance 3

But when I try to create a relation between Dates from the query Calendrier and Relance 2 and Relance 3 from tracking query it says I can't create 2 relations. Would you know how I can fix that?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Relationships will propagate filters between tables so it is not allowed to have multiple active relationships between tables to avoid ambiguity. If you are not familiar with active/inactive relationships, you can refer to this guide: Active vs inactive relationship guidance 

In addition, the RELATED function requires that an active relationship exists between the current table and the table with related information. If you want to get the +7 working date according to Relance 2 or Relance 3, you can try below code. This code is not dependent on the relationship. 

Column 2 =
MAXX (
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] = EARLIER ( 'Tracking (2)'[RELANCE 2] )
    ),
    'Calendar'[+ 7 Working Days]
)

If this is not what you want, can you please show what you want to do with Relance 2 and Relance 3?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thank you so much @v-jingzhang !!!!! It's exactly what I wanted and it works !!!!

[amitchandak]

@Anonymous , Check this

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

refer comments if you are looking for a column

[Anonymous]

Hi @amitchandak ,

thank you for your answer. I don't understand everything. I Think I have done that. I have this:

The problem is that I want to add the 7 working days to another date column in a another request. 

That second part, I don't get?:

Plus 10 Days = var _max =maxx(ALLSELECTED('Date'),'Date'[Work Date cont Rank])
return
CALCULATE(Min('Date'[Date]),filter(ALL('Date'),'Date'[Work Date  Rank] =_max+10))

Rolling Last 10 Days =  CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Work Date cont Rank]>=min('Date'[Work Date cont Rank])-10 
				&& 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank])))