Forum Discussion
Post ProdigyCalculate Date Durations between 2 dates
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.
Super UserHi 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.
icassiemThank 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
- icassiemended up doing: AVERAGE(SalesForce_SalesStageVelocity[duration_Day])i am now trying to place the average between 2 dates (created and modified)DurationAVG_1 =var _rangeStart = SalesForce_SalesStageVelocity[SalesStageVelocitySelectedStartDate]var _rangeEnd = SalesForce_SalesStageVelocity[SalesStageVelocitySelectedEndDate]return//if(//SELECTEDVALUE(SalesForce_SalesStageVelocity[HistoryCreatedDateMin])>=_rangeStart//&& SELECTEDVALUE(SalesForce_SalesStageVelocity[HistoryModifiedDateMax])>_rangeEnd//,AVERAGE(SalesForce_SalesStageVelocity[duration_Day])//,0)CALCULATE(AVERAGE(SalesForce_SalesStageVelocity[duration_Day]),FILTER / SELECTEDVALUE(SalesForce_SalesStageVelocity[HistoryCreatedDateMin])>=_rangeStart )please help, i am trying to filter then only avg on the filtered date range between the create and mod date
- icassiem
Hi,
Any update on calculating the average between 2 date fields "from and to"?
Regards