Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Adding working days

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 th...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
rohit_singh
Icon for Solution Sage rankSolution Sage
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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?