CALCULATE TIME INTERVAL WITH CONDITIONS

[SIGSME]

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. 

[v-robertq-msft · Accepted solution]

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.

[GilbertQ]

Hi @SIGSME 

What you would need to do is to convert your times to seconds.

Then create the measures based on the seconds and finally display the final outcome using a formatting option.

Here is my blog post with more details: Convert from Seconds to Minutes with a DAX Measure - Reporting/Analytics Made easy with FourMoo and ...

[SIGSME]

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