Forum Discussion
Calculate Date Durations between 2 dates
- 1 year ago
Hi icassiem - your suggested method of using a calculated column for individual durations and a calculated measure for aggregation is correct.
using datediff. calculated column calculate duration days:
Duration_Days =
DATEDIFF(
TableName[StartDate],
TableName[EndDate],
DAY
)create a measure for total duration as
Total_Duration =
SUM(TableName[Duration_Days])Convert Duration to Hours or Mixed Format
Duration_Format =
VAR Days = INT(TableName[Duration_Hours] / 24)
VAR Hours = MOD(TableName[Duration_Hours], 24)
RETURN
Days & " days " & Hours & " hours"In your visual e.g., a table or matrix, add the "Stage" field to the rows.Add the Total_Duration measure to display the summed duration per stage.
Thank You both rajendraongole1 and Anonymous
i used rajendraongole1 approach, Anonymous as i was unsure of calculations having both max when i have in pivot visual slicing and dicing
I think i have it incorrect with the total and it needs to be an average time per stage or sub stage
How would I change the total duration to average if it is by opportunity, stage and sub stage, does it mean i have to calc the distinct opportuntity first "sales force data"?
please help
- icassiem1 year ago
Post Prodigy
Hi,
Any update on calculating the average between 2 date fields "from and to"?
Regards