Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
4 years ago
Solved

Accumulative Average calculation using difference in dates

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

 

 

 
 
 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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)

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      romovaro
      Responsive Resident
      4 years ago

      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)
       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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