Forum Discussion
Calculate Duration Datetime and Sum
Greetings, i am trying to get a duration of a single column which contains the datetime and after that i need to sum that duration between the dates i want to get which i gona show in a example.
First Examle of my DateTime With N values: (Duration should be likeB= B - A = 10m ; C = C-B = 10 and .... same to the others probably the first value needs to be 0 since there is nothing before). Sounds easy on C# but i dont know how to do this on powerBI.
Column: DaTeTime
20/05/2012 01:20:00 (A)
20/05/2012 01:30:00 (B)
20/05/2012 01:40:00 (C)
The next step after i get a Duration column would be to sum that duration based of the purple area (by saying this i am saying i want to get all datetime(duration) inside purple area and sum that.
How can i achieve this on powerbI (on first step get duration column and next get duration inside the purple area) is this possible?
8 Replies
UpdateInfo:
First Step off getting the duration is already done, which i achieve this using this :
Duration = IF(DATEDIFF(LOOKUPVALUE(X[Time],X[Index],X[Index]-1),X[Time],MINUTE)>720,BLANK(),DATEDIFF(LOOKUPVALUE(X[Time],X[Index],X[Index]-1),X[Time],MINUTE))2Step now is where it becomes hard, since i want to sum the duration from the time inside that purple area i dont have aclue how to make this here. Is like i want to put, for example, SUM(Duration FROM TIME WHERE LINE IS 1) .The Row(Line) that makes the purple area you can watch on the image have values 0 and 1, meaning that when is 1it makes the purple area when is 0 dont exist area.Hi,
Try this measure
=SUM([Duration])
Hope this helps.
That will sum all duration and not only the duration that belong to the purple area on the image, so that will never work for this case.
I need something like this example https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586, but i dont have the started repair and end repair date witch i dont know to apply this to my case.
