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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Turn Around Time

Hi, I have done turn around time (TAT) in Excel using NETWORKDAYS formula.

Here is the example below. How can I do this in Power BI using Contact Date & Date Written?

 

TAT.PNG

 

Thanks,

spanda

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Sure thing. I will use your first table as the starting point.  You can use excel to build a date table and then import, or use the CALENDAR function in DAX to create one.  I went with the DAX.

 

Calculations-->Modeling-->New Table.  Use the CALENDAR function.  Which needs a start and end date (which will cover in a sec).  Now, could use CALENDARAUTO which will find the start and end data automatically, but does so by searching the entire data model.  Would have worked fine in this small example, but in bigger data models that have date place holders as something like 1/1/9999, CALENDARAUTO will find that as the max.  So back to our CALENDAR function.  Need to find the Min and Max.  We wil do this by search each the Contact Date and Date writting columns and use the Min and Max of those two columns:

Date = 
CALENDAR(
	MIN (
		MIN (TurnAroundTIme[Contact Date]),
		MIN( TurnAroundTIme[Date Written])
	),
	MAX (
		MAX (TurnAroundTIme[Contact Date]),
		MAX( TurnAroundTIme[Date Written])
	)
)

Then add a calculated column for Day Name and then one to label using that day name to denote Weekday or Weekend:

Day = FORMAT('Date'[Date],"DDDD")

Day Type = 
SWITCH(
	'Date'[Day],
	"Saturday", "Weekend",
	"Sunday", "Weekend",
	"Weekday"
)

Here's the end result:

Date Table.png

 

Then need to mark as  Date Table so DAX knows to use that for the built-in time intelligence functions:

Mark as date table.png

Now that we have a calendar that we can use, setting up the DAX formulas should be much easier.   There's actually two ways we can do this, one using FILTER and one using DATESBETWEEN.  

TaT using Filter = 
CALCULATE(
    COUNTROWS( 'Date' ),
        FILTER( ALL('Date'),
		    MAX( TurnAroundTime[Contact Date]) <= 'Date'[Date]
		    && MAX( TurnAroundTime[Date Written]) >= 'Date'[Date]
            && 'Date'[Day Type] ="Weekday"
	    )
)

TaT using DatesBetween =
 CALCULATE(
	COUNTROWS( 'Date' ),
       DATESBETWEEN('Date'[Date],
		MAX( TurnAroundTime[Contact Date]) ,
		MAX( TurnAroundTime[Date Written])
    ),
    'Date'[Day Type] ="Weekday"
)

DATESBETWEEN leverages the use of the time-intelligence, but requires some additional logic to not give a figure when there is no Contact Date or Date Written field:

TaT using DatesBetween = 
IF (
    NOT
        OR(
         ISBLANK(MAX( TurnAroundTime[Contact Date])), 
         ISBLANK(MAX( TurnAroundTime[Date Written])
         )
    ),
    CALCULATE(
	    COUNTROWS( 'Date' ),
          DATESBETWEEN('Date'[Date],
	    	MAX( TurnAroundTime[Contact Date]) ,
	    	MAX( TurnAroundTime[Date Written])
      	),
    'Date'[Day Type] ="Weekday"
    )
 )

Then the final output:

Final Table.png

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

For the least amount of typing do this in the Power Query Editor. 

Select the two Columns- 

Go tot he Add Column Ribon at the top and slecet the Date drop down in "From Date & Time"  and click "Subtract Days" 

 

 

Hope that helps 

Anonymous
Not applicable

Thank you @Anonymous

It works...But for same date it should say 1 day not 0

Also it shows numbers in negative like difference between 11/1/18 & 11/2/18 is showing -2.

 

Thanks,

panda2018

Anonymous
Not applicable

Part of the benefit of doing it that way is it shows you negative digits where as the Measures will result in an error. This allows you to filter them out later (in  measures ) or correct your underlying data that has errors (for example when items are "delivered" before the order is place. This is a data error) 

 

Esp where a blank might default to 01/01/1900 

LivioLanzo
Solution Sage
Solution Sage

If you dont want have a date table you can do it like this:

 

=
COUNTROWS(
    FILTER(
        CALENDAR( [Contact Date], [Date Written] ),
        WEEKDAY( [Date], 2 ) < 6
    )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

Thanks @LivioLanzo

Its showing me this message now.

 

TAT 1.PNG

 

 

Thanks for your help!!

 

Hi @Anonymous

 

as the error message says, you have some start dates which are after the end date, so you need to add a condition for this scenario

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

Thank  you @LivioLanzo!!

So how & where should I add the condition ? 

 

-Panda2018

@Anonymous,

 

You may just add IF Function as follows.

Column =
IF (
    Table1[Contact Date] > Table1[Date Written],
    BLANK (),
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you @LivioLanzo !!

 

So I am trying to count the days it takes to write a note (Difference between Contact date & Date Written Date)

And it should not count the weekends or Holidays.

 

I tried this formula below. It works but it is counting the weekends & Holidays .

How should I do it? 

 

TAT = IF('2018-2019'[Contact Date]>'2018-2019'[Date Written],0 - DATEDIFF('2018-2019'[Date Written],'2018-2019'[Contact Date],DAY),DATEDIFF('2018-2019'[Contact Date],'2018-2019'[Date Written],DAY))

 

Thanks,

panda2018

Can you post another dataset along with expected results?

 

thanks

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

@LivioLanzo Here is the data.

 

TAT 2.PNG

 

Hopw do I attach a Excel file?

Thanks!!

Anonymous
Not applicable

Easiest thing to do is get a dedicated Date Table with a column that denotes workday or not.  Plus having a dedicated date table opens up all the time intelligence functions.

Anonymous
Not applicable

Can you show me in an example hoew to?

Thanks, & how do I share an dataset?

 

@Anonymous Thanks!

Anonymous
Not applicable

Sure thing. I will use your first table as the starting point.  You can use excel to build a date table and then import, or use the CALENDAR function in DAX to create one.  I went with the DAX.

 

Calculations-->Modeling-->New Table.  Use the CALENDAR function.  Which needs a start and end date (which will cover in a sec).  Now, could use CALENDARAUTO which will find the start and end data automatically, but does so by searching the entire data model.  Would have worked fine in this small example, but in bigger data models that have date place holders as something like 1/1/9999, CALENDARAUTO will find that as the max.  So back to our CALENDAR function.  Need to find the Min and Max.  We wil do this by search each the Contact Date and Date writting columns and use the Min and Max of those two columns:

Date = 
CALENDAR(
	MIN (
		MIN (TurnAroundTIme[Contact Date]),
		MIN( TurnAroundTIme[Date Written])
	),
	MAX (
		MAX (TurnAroundTIme[Contact Date]),
		MAX( TurnAroundTIme[Date Written])
	)
)

Then add a calculated column for Day Name and then one to label using that day name to denote Weekday or Weekend:

Day = FORMAT('Date'[Date],"DDDD")

Day Type = 
SWITCH(
	'Date'[Day],
	"Saturday", "Weekend",
	"Sunday", "Weekend",
	"Weekday"
)

Here's the end result:

Date Table.png

 

Then need to mark as  Date Table so DAX knows to use that for the built-in time intelligence functions:

Mark as date table.png

Now that we have a calendar that we can use, setting up the DAX formulas should be much easier.   There's actually two ways we can do this, one using FILTER and one using DATESBETWEEN.  

TaT using Filter = 
CALCULATE(
    COUNTROWS( 'Date' ),
        FILTER( ALL('Date'),
		    MAX( TurnAroundTime[Contact Date]) <= 'Date'[Date]
		    && MAX( TurnAroundTime[Date Written]) >= 'Date'[Date]
            && 'Date'[Day Type] ="Weekday"
	    )
)

TaT using DatesBetween =
 CALCULATE(
	COUNTROWS( 'Date' ),
       DATESBETWEEN('Date'[Date],
		MAX( TurnAroundTime[Contact Date]) ,
		MAX( TurnAroundTime[Date Written])
    ),
    'Date'[Day Type] ="Weekday"
)

DATESBETWEEN leverages the use of the time-intelligence, but requires some additional logic to not give a figure when there is no Contact Date or Date Written field:

TaT using DatesBetween = 
IF (
    NOT
        OR(
         ISBLANK(MAX( TurnAroundTime[Contact Date])), 
         ISBLANK(MAX( TurnAroundTime[Date Written])
         )
    ),
    CALCULATE(
	    COUNTROWS( 'Date' ),
          DATESBETWEEN('Date'[Date],
	    	MAX( TurnAroundTime[Contact Date]) ,
	    	MAX( TurnAroundTime[Date Written])
      	),
    'Date'[Day Type] ="Weekday"
    )
 )

Then the final output:

Final Table.png

What if want the average turn around time at any given time?

Anonymous
Not applicable

Thank you so very much!!! @Anonymous

 

-panda2018

@Anonymous

 

It depends on what you are trying to achieve. What should happen when your start date is after your end date ?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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