Accumulative Average calculation using difference in dates

[romovaro]

Hello

I have a question about creating accumulative measures on Power BI 

I have a table with different tasks and Assigned date and Completed Date. I have a formula to get the difference (in days) between both dates:

_DateDiferrence =

VAR

Sum_Date=Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2]))

RETURN

if(sum_Date>=0,Sum_Date,0)

With the diff in days, I can show the average days x step (task).

Now I would like to create a different table showing the cumulative in days of the Total process.

Any help, pls? thanks

[Anonymous]

Hi @romovaro 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regard

Community Support Team _ Ailsa Tao

[amitchandak · Accepted solution]

@romovaro , Assume You want sum till step level sum and avg after that

_DateDiferrence =
VAR
Sum_Date=AverageX( Values('BMPS LOF Report'[Step] ) , Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2])))
RETURN
if(sum_Date>=0,Sum_Date,0)

[romovaro]

Hello Amitchandak

I created the new field as you described but it shows 0 in all the steps.

I believe I am doing something wrong

DateDiference2 =

VAR

Sum_Date=AverageX( Values('BMPS LOF Report'[Steps] ) , Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2])))

RETURN

if(sum_Date>=0,Sum_Date,0)