Forum Discussion
Duration management
Hi all, I have an issue that is too specific to find existing discussions about it: So I have calculated a duration measure to know the response time excluding weekends. I found the below piece of...
Hello,
this is great indeed
This recalculates the delay between start and end date but ignores the working days table.
(This piece of code I had)
Delay =
VAR _Start = 'ALL Responded Case Email'[Created On (Email) (Email)]
Var _End = 'ALL Responded Case Email'[First Manual Response]
Return SUMX(
CALCULATETABLE(
Calendar_Table,
DATESBETWEEN(Calendar_Table[Date],_Start,_End),
Calendar_Table[Workday] = 1
),
MAX(MIN(Calendar_Table[End],_End) - MAX(Calendar_Table[Start],_Start),0) * 24
)
I'm a bit confused between your bit that is creating a new table and my bit that is creating a new column within a table.
I will try and combine them myself, but if this is quick for you, may I ask for some help?
I will try and combine them myself, but if this is quick for you, may I ask for some help?
Hi Anonymous - the DAX code was only an example. Essentially, I have used the code to add a column to a table. However you can skip this to use the DateDiff function in a measure. However, since the DateDiff result could be used in many aggregate measures (i.e. SUM, MIN, MAX, AVERAGE), I would recommend adding an column to perform the calculation when the Data is refreshed, rather that including in SUMX, MINX, MAXX expressions. As for your CALCULATETABLE, I would need more information about the data model with same data to understand what you are trying to achieve.