topic Re: Add X network days to start date in DAX Commands and Tips

Add X network days to start date

DennisSchlein — Thu, 18 Apr 2024 12:10:18 GMT

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?

Re: Add X network days to start date

Dangar332 — Thu, 18 Apr 2024 12:24:40 GMT

Hi, DennisSchlein

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

Blog Link

Re: Add X network days to start date

Anonymous — Fri, 19 Apr 2024 08:42:53 GMT

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!

Re: Add X network days to start date

DennisSchlein — Fri, 19 Apr 2024 09:41:12 GMT

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

Re: Add X network days to start date

Anonymous — Mon, 22 Apr 2024 08:06:55 GMT

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

Re: Add X network days to start date

DennisSchlein — Mon, 22 Apr 2024 09:16:56 GMT

Hi

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

Onedrive Sample File

Thank you for the effort! 🙂

Re: Add X network days to start date

Anonymous — Fri, 26 Apr 2024 08:20:15 GMT

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

Re: Add X network days to start date

DennisSchlein — Mon, 29 Apr 2024 09:40:39 GMT

Hi Anonymous

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

Re: Add X network days to start date

Dangar332 — Thu, 09 May 2024 07:05:01 GMT

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.