Time Differencce between date base on slicer and 4 diferent dates

[oscaag]

Hello Community,

I am trying to calculate the time that a device is unavailable (dropped) and I need to create a measure to calculate the time difference (in seconds) based on the date slicer, but taking into account that the date of creation of the device can be greater than the minimum time of slicer, in such a case the difference would be between date of creation and end date of slicern. Also the time of destruction of the equipment can be earlier than the maximum date of the slicer, in that case the difference would be between the minimum time slicer or the date of creation and the date of device destruction.
All date fields are in the same table.

I have created a measure with variables step by step to obtain all the values but I can not do the subtraction of dates since the variable 'start_time_calculated', does not show me results. Idem for the 'end_time_calculated.

This is my table wiht the slicer and the fields for one device where I need to have the seconds down.
This is my measure approach:

__Measure T. Down =

VAR device_create_time =

    MINX(outage, outage[create_time] )  

VAR device_destroy_time=  

    MINX(outage, outage[destory_time] )

VAR FirstDateInFilter =

 MINX (ALLSELECTED ( 'Date' ),

        'Date'[Date] )

VAR LastDateInFilter =

    MAXX (ALLSELECTED ( 'Date' ),

        'Date'[Date] )

VAR start_time_calculated = // if device create in the filter period, we use this as start time, if not min of filter time

        IF(  

        device_create_time > FirstDateInFilter,

        device_create_time,

        FirstDateInFilter

        )

VAR end_time_calculated = // if device destroyed in the filter period, we use this as end time, if not max of filter time

    IF(  

        LastDateInFilter > device_destroy_time ,

        device_destroy_time,

        LastDateInFilter

        )

VAR result = CALCULATE(

    DATEDIFF(end_time_calculated, start_time_calculated,SECOND),

     outage[outage_type] = 1)   //time in seconds??

RETURN result

Can someone help me get the correct measure?

Thanks

[amitchandak]

@oscaag ,

Based on what I got, create a new measure

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
sumx(Table, datediff(max([Created_date], _min), min([end_time], _max), day) )

[oscaag]

Hello @amitchandak,

Thanks for your response but I miss the control over the time when we use the slicer, so the VARs to control it.
I attach a report with the data. ( Report ) 

I have create separated measures for my variables and create two tables one with the fields and one with the measure.

What I need is the durantion in second of the devices downs but taking into account that if the date selected in the slicer is greater than the starting of the down we should consider the slicer time for the result.

Many thanks.