Calls Received within 48 HOURS of case being closed

[axk180022]

Hi All,

Below is the table containing multiple serial numbers, Ive taken just one serial number for example.

For each serial number the cases are open which is the Actualstart and close case is the actualend. 

So If a case gets open within 48 hours or the Actualend, could you please tell me how to calculate the difference . You can see one such call below that was opened again within 48hours. Can you tell me how to get the "call duration table"

Thanks in advance

Serial   Number	Call  Type	SI Status	Activity	Actualstart	Actualend	Callduration
ZCV-S777	CM	green	Arrived on Site	9/29/2020 8:00:00 AM	9/29/2020 8:00:00 AM
ZCV-S777	CM	green	Phone Repair	6/6/2021 3:00:00 PM	6/6/2021 3:16:52 PM
ZCV-S777	CM	yellow	Phone Repair	8/11/2021 6:33:17 PM	8/11/2021 9:30:00 PM
ZCV-S777	FIELD-ACT	green	Arrived on Site	8/12/2021 12:00:00 PM	8/24/2021 12:00:00 PM	<48 hours
ZCV-S777	CM	green	Phone Repair	10/18/2021 8:30:00 AM	10/18/2021 9:30:00 AM
ZCV-S777	CM	yellow	Phone Repair	9/24/2021 7:04:56 PM	9/24/2021 8:42:06 PM

@amitchandak @Ashish_Mathur 

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(Data[Actualstart]-CALCULATE(MAX(Data[Actualend]),FILTER(data,Data[Serial Number]=EARLIER(Data[Serial Number])&&Data[Actualend]<EARLIER(Data[Actualstart])))<=2,"<48 hours",BLANK())

Hope this helps.

[axk180022]

Hi,

Is it possible to create a measure instead of calculated column? since it involves lot of filters. so the front end report looks like the one above but the actual table has lot of columns. 

@Ashish_Mathur 

[Ashish_Mathur]

A calculated column formula is best suited for a calculation like this.  I am not sure of how to get your desired result via a measure. 

[axk180022]

If there are other tables in the model, how do we include that filters in the calculated column?

for instance I have another table (caseline) that has a column name 'statuscode' to be filtered as "Resolved".

similarly another table (activity) which has a column name 'code'  which needs to be filtered as "CM"

[Ashish_Mathur]

Hi,

Could you share some data and show the expected result.

[sturlaws]

Hi, @axk180022,

based on how I interpret your question, I suggest this code for a calculated column:

Column =
VAR _serialNumber = 'Table'[Serial_Number]
VAR _rank = 'Table'[ranking]
VAR _currentEndDate = 'Table'[Actualend]
VAR _nextStartDate =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Actualstart] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ranking] = _rank + 1
                && 'Table'[Serial_Number] = _serialNumber
        )
    )
VAR _nextEndDate =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Actualend] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ranking] = _rank + 1
                && 'Table'[Serial_Number] = _serialNumber
        )
    )
VAR _isWithin48Hours =
    IF (
        _nextStartDate <= _currentEndDate + 2
            && NOT ( ISBLANK ( _nextStartDate ) ),
        1,
        0
    )
VAR _minute =
    IF (
        _isWithin48Hours,
        DATEDIFF ( 'Table'[Actualstart], _nextEndDate, MINUTE ),
        DATEDIFF ( 'Table'[Actualstart], 'Table'[Actualend], MINUTE )
    )
RETURN
    _minute

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[axk180022]

Hi,

The table does not have any column as ranking can you tell me what is it?

Thank you,

Akshatha

[sturlaws]

crikey, sorry, a calculated column like this:

Ranking =
VAR _currentSerial = 'Table'[SerialNumber]
RETURN
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[SerialNumber] = _currentSerial ),
        'Table'[ActualStart],
        'Table'[ActualStart],
        asc
    )

[axk180022]

Hi,

Is it possible to create a measure instead of calculated column? since it involves lot of filters. so the front end report looks like the one above but the actual table has lot of columns. 

[sturlaws]

In theory it is possible, but how to write it will depend on your use case. Please provide a pbix-file which resembels your real report as much as possible. It should contain the model you are using, and quite a bit more of the data you provided in your original post

[axk180022]

I am not getting an option to insert the file. @sturlaws 

[sturlaws]

Upload to dropbox/onedrive/other and share the link