Forum Discussion

DennisSchlein's avatar
DennisSchlein
Icon for Helper III rankHelper III
2 years ago

Add X network days to start date

Hi 

Im working on a forecast model, where I have a start date and a amount of working days to completion.

Could be
StartDate
18-04-2024

Exptected Task Working Days : 
5


Would expect it to return 25-04-2024.


And I cannot find a good day to do it?


hekp

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DennisSchlein ,

     

    Thanks for the reply fom Dangar332 .

     

    Here is how I did it:

     

    This is my orignal data:

    Date

    Period

    2024-04-01

    5

    2024-04-02

    5

    2024-04-03

    5

    2024-04-04

    5

    2024-04-05

    5

    2024-04-06

    5

    2024-04-07

    5

    2024-04-08

    5

    2024-04-09

    5

    2024-04-10

    5

    2024-04-11

    5

    2024-04-12

    5

    2024-04-13

    5

    2024-04-14

    5

    2024-04-15

    5

    2024-04-16

    5

    2024-04-17

    5

    2024-04-18

    5

    2024-04-19

    5

    2024-04-20

    5

    2024-04-21

    5

    2024-04-22

    5

    2024-04-23

    5

    2024-04-24

    5

    2024-04-25

    5

    2024-04-26

    5

    2024-04-27

    5

    2024-04-28

    5

    2024-04-29

    5

    2024-04-30

    5

     

    Created a calculated column:

     

    End Date = 
IF (
    NOT WEEKDAY (
        DATE ( YEAR ( 'DateTable'[Date] ), MONTH ( 'DateTable'[Date] ), DAY ( 'DateTable'[Date] ) ),
        2
    )
        IN { 6, 7 },
    DATE ( YEAR ( 'DateTable'[Date] ), MONTH ( 'DateTable'[Date] ), DAY ( 'DateTable'[Date] ) + 'DateTable'[Period] + 2 )
)

     

     

    The final page effect is as follows:

     

    pbix file is attached.

     

    If this does not meet your expected results, perhaps you could provide me with your pbix file? Remember not to log into your account on Power BI Desktop when uploading the pbix file.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • DennisSchlein's avatar
      DennisSchlein
      Icon for Helper III rankHelper III
      2 years ago

      Hi Anonymous 

      You model does it as i expect mine to.
      But I can have different period values per day, depending on tast type.

      I tried adding a modified logic from yours into my model, but its not quite right.
      (it sayd The file type .pbix is not supported? When trying to add it here?



      Added a sample file to my power bi space.
      app.powerbi.com - Pbix link 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi DennisSchlein ,

        I can not open the pbix link you shared, maybe you can upload it somewhere else? Like Google Drive, Onedrive or Dropbox?

         

         

        Please remember not to log in to your account in Power BI Desktop when uploading pbix file.

         

        Best Regards,
        Yang
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

         

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, DennisSchlein 

    Sorry for late reply i am working on it 

    Try below measure

    result = 
var a = CALENDAR(MIN('sample'[CMR_Close])+1,MIN('sample'[CMR_Close])+MIN('sample'[E2E])*2)
var b = ADDCOLUMNS(a,"week",IF(WEEKDAY([Date],2) in {6,7},0,1))
var c = ADDCOLUMNS(b,"rank",var c1=SUMX(FILTER(b,[Date]<=EARLIER([Date])),[week]) RETURN IF(WEEKDAY([Date],2) in {6,7},0,c1))
var d = MAXX(FILTER(c,[rank]=MIN('sample'[E2E])+0),[Date])
var e= CONCATENATEX(c,[rank]," , ",[Date])
RETURN
d




    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.