Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DennisSchlein
Helper III
Helper III

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?


8 REPLIES 8
Dangar332
Super User
Super User

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

Dangar332_0-1715238254783.png


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
Community Support
Community Support

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:

vhuijieymsft_0-1713516100109.png

 

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!

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?

image.png

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

Hi @DennisSchlein ,

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

 

vhuijieymsft_0-1713773033309.png

 

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!

 

Hi 

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

Onedrive Sample File 


Thank you for the effort! 🙂 

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:

vhuijieymsft_0-1714119552040.png

 

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!

Hi @v-huijiey-msft 

image.png

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
Super User
Super User

Hi, @DennisSchlein 

 

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

Blog Link 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.