Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding 1 weekday to date

Deal TypeDateNew Date
DMT28 Apr 
ABC28 Apr 
XYZ29 Apr 

 

Hi

 

I want to create a new column and where deal type = DMT Or ABC, I want the New Date to be Date + 1 Weekday so that the dates align with Deal Type XYZ. 

 

Could you tell me how I can 1 weekday onto a date

 

Thanks

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    If you want to add one day,please try calculated column as below:

     

    New Date =
IF ( 'Table'[Deal Type] IN { "DMT", "ABC" }, 'Table'[Date] + 1, 'Table'[Date] )

     

    If you want to add one working day,please try calculated column as below:

     

    New Date2 = 
IF (
    'Table'[Deal Type] IN { "DMT", "ABC" },
    SWITCH (
        WEEKDAY ( 'Table'[Date], 2 ),
        5, 'Table'[Date] + 3,
        6, 'Table'[Date] + 2,
        'Table'[Date] + 1
    ),
    'Table'[Date]
)

     

    Please check my attached pbix file for more details.

     

    If it doesn't meet your requirement ,please share your expected result in excel.

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Great thank you, I'll take a look 🙂

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, Anonymous 

        If you want to add one day,please try calculated column as below:

         

        New Date =
IF ( 'Table'[Deal Type] IN { "DMT", "ABC" }, 'Table'[Date] + 1, 'Table'[Date] )

         

        If you want to add one working day,please try calculated column as below:

         

        New Date2 = 
IF (
    'Table'[Deal Type] IN { "DMT", "ABC" },
    SWITCH (
        WEEKDAY ( 'Table'[Date], 2 ),
        5, 'Table'[Date] + 3,
        6, 'Table'[Date] + 2,
        'Table'[Date] + 1
    ),
    'Table'[Date]
)

         

        Please check my attached pbix file for more details.

         

        If it doesn't meet your requirement ,please share your expected result in excel.

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Maybe:

     

    New Date = 
 VAR __Dates = ADDCOLUMNS(CALENDAR([Date]+1,[Date]+3),"__Weekday",WEEKDAY([Date],2))
RETURN
 MINX(FILTER(__Dates,[__Weekday]<6),[Date])

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, thanks for your quick response.

      Sorry a bit new, could you tell me how I would put the IF statement in there that says IF the deal type is DMT or ABC then use the formula above to add 1 week day, otherwise, use the existing date (if the deal type is XYZ)