Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
axk180022
Helper II
Helper II

Calls Received within 48 HOURS of case being closed

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-S777CMgreenArrived on Site9/29/2020 8:00:00 AM9/29/2020 8:00:00 AM 
ZCV-S777CMgreenPhone Repair6/6/2021 3:00:00 PM6/6/2021 3:16:52 PM 
ZCV-S777CMyellowPhone Repair8/11/2021 6:33:17 PM8/11/2021 9:30:00 PM 
ZCV-S777FIELD-ACTgreenArrived on Site8/12/2021 12:00:00 PM8/24/2021 12:00:00 PM<48 hours
ZCV-S777CMgreenPhone Repair10/18/2021 8:30:00 AM10/18/2021 9:30:00 AM 
ZCV-S777CMyellowPhone Repair9/24/2021 7:04:56 PM9/24/2021 8:42:06 PM 

 

@amitchandak @Ashish_Mathur 

 

12 REPLIES 12
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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"

Hi,

Could you share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sturlaws
Resident Rockstar
Resident Rockstar

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.

Hi,

 

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

 

Thank you,

Akshatha

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
    )

 

 

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. 

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

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

Upload to dropbox/onedrive/other and share the link

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.