Forum Discussion

SIGSME's avatar
SIGSME
New Member
4 years ago
Solved

CALCULATE TIME INTERVAL WITH CONDITIONS

I am trying to get the result out from the time interval table. On Certain Conditions. 

Here is my data I have Condition Colum with different variables "START" "END" RESUME. I want to calculate time interval in minutes based on conditions from START to END where my SLine = Sline and Line = Line and Docno = DocNo 

Total Time = CONDITION(START-END)

BREAK TIME = CONDITON SUM(BRK+END)

Process Time = Total Time - Break Time

 

My result will look similar to the one below. 

 

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I created some test data to achieve your requirements, you can try my steps:

    This is the test data I created based on your data model:

     

    I created three measures like this:

    Total Time =

var _start=CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[DocNo]=MAX('Table'[DocNo])&&[Line]=MAX('Table'[Line])&&[SLine]=MAX('Table'[SLine])&&[Status]="Start"))

var _end=CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[DocNo]=MAX('Table'[DocNo])&&[Line]=MAX('Table'[Line])&&[SLine]=MAX('Table'[SLine])&&[Status]="End"))

return

DATEDIFF(_start,_end,MINUTE)
    Breaktime = SUM('Table'[Break Time])
    Process Time = [Total Time]-[Breaktime]

    Then I create a table chart and place it like this:

     

    And you can get what you want.

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • SIGSME's avatar
      SIGSME
      New Member
      4 years ago

      HI GI

      I Understand that this needs to be converted into seconds but how will do the rest out of it as i am totally new to PBI

      Here is my data I have Condition Colum with different variables "START" "END" RESUME. I want to calculate time interval in minutes based on conditions from START to END where my SLine = Sline and Line = Line and Docno = DocNo 

      Total Time = CONDITION(START-END)

      BREAK TIME = CONDITON SUM(BRK+END)

      Process Time = Total Time - Break Time

       

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I created some test data to achieve your requirements, you can try my steps:

    This is the test data I created based on your data model:

     

    I created three measures like this:

    Total Time =

var _start=CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[DocNo]=MAX('Table'[DocNo])&&[Line]=MAX('Table'[Line])&&[SLine]=MAX('Table'[SLine])&&[Status]="Start"))

var _end=CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[DocNo]=MAX('Table'[DocNo])&&[Line]=MAX('Table'[Line])&&[SLine]=MAX('Table'[SLine])&&[Status]="End"))

return

DATEDIFF(_start,_end,MINUTE)
    Breaktime = SUM('Table'[Break Time])
    Process Time = [Total Time]-[Breaktime]

    Then I create a table chart and place it like this:

     

    And you can get what you want.

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.