Add X network days to start date

[DennisSchlein]

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?

[Dangar332]

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.

[v-huijiey-msft]

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]

Hi @v-huijiey-msft 

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 

[v-huijiey-msft]

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!

[DennisSchlein]

Hi 

I give full access to the foler, to this onedrive folder with the sample file.

Onedrive Sample File 


Thank you for the effort! 🙂 

[v-huijiey-msft]

Hi @DennisSchlein ,

Please try this new syntax:

End Date =
VAR _date = DATE(YEAR('sample'[CMR_Close]),MONTH('sample'[CMR_Close]),DAY('sample'[CMR_Close])+'sample'[E2E])
VAR _dateadd2 = DATE(YEAR('sample'[CMR_Close]),MONTH('sample'[CMR_Close]),DAY('sample'[CMR_Close])+'sample'[E2E])+2
RETURN
IF(WEEKDAY(_date,2)IN{6,7},_dateadd2,_date)

The visual effect of the page is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

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]

Hi @v-huijiey-msft 



It's close, but there are some odd ones. As shows in the picture the same startdate with 2 different E2E values returns the same date?

Thank you for the effort

[Dangar332]

Hi, @DennisSchlein 

you can refer my blog on community it is based on what you want

Blog Link