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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Beomere
Regular Visitor

Can't calculate days with two formulas in 1 column

I have 2 columns amongst many:

 

Created Date and Completed Date

 

All cells under Created Date have an entry, but not all under Completed Date do. I'd like to create a Custom Column with a formula that tells me the number of days (in a 7 day week, not a 5 day working week) between Completed and Created Date; and where no Completed Date exists, between Today's Date (which is dynamic) and the Created Date. Can anyone please help as I can't understand IF formulas. 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Beomere ,

A new column

 

Total days =

if(not(isblank([Completed Date])), datediff( [Created Date] ,[Completed Date], day), blank())

 

or

Business Days =

if(not(isblank([Completed Date])), COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created Date],Table[Completed Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)), blank())

 

View solution in original post

2 REPLIES 2
Beomere
Regular Visitor

got it now, thank you!

 

amitchandak
Super User
Super User

@Beomere ,

A new column

 

Total days =

if(not(isblank([Completed Date])), datediff( [Created Date] ,[Completed Date], day), blank())

 

or

Business Days =

if(not(isblank([Completed Date])), COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Created Date],Table[Completed Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)), blank())

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.