Forum Discussion
Calculate Turn Around Time
- Anonymous7 years ago
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:
Then need to mark as Date Table so DAX knows to use that for the built-in time intelligence functions:
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:
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
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