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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rana80
Frequent Visitor

Datediff with filter from same column

I need to calculate the date difference between two dates in the same column. I cant seem to find an answer for this and would like to learn how to do this in PowerBi instead of excel.

 

The below data has 2 Task IDs (A & B) but there is a sequnce of call centre users interactions based on the time for the task id which i have labeled the sequence as 'chat sequence' in ascending order.

Is there a way as a DAX measure or calculated column that looks at the date diff for the the first 2 chat seqeunce only ( chat sequnce 1&2) for each task id.

 

So for Task ID A, the difference between chat seqeunce 1 & 2 should be 00:11:35

Task ID B, should be 00:06:43

Rana80_2-1695216444547.png

 

 

I've tried something like the below but i cant get it to work.

 

Rana80_1-1695216372637.png

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

and you can try this

Diff3 = VAR _Top =
    TOPN(
        2,
        SUMMARIZE(
            'Table',
            'Table'[Task ID],
            'Table'[chat sequence],
            'Table'[Created On]
        ),
        'Table'[chat sequence], ASC
    )
VAR _Result =
     MAXX( _Top, 'Table'[Created On] ) -
        MINX( _Top, 'Table'[Created On] )
RETURN 
    FORMAT ( INT ( _Result ), "0:" ) & FORMAT ( _Result, "hh:mm:ss" )

View solution in original post

3 REPLIES 3
Ahmedx
Super User
Super User

and you can try this

Diff3 = VAR _Top =
    TOPN(
        2,
        SUMMARIZE(
            'Table',
            'Table'[Task ID],
            'Table'[chat sequence],
            'Table'[Created On]
        ),
        'Table'[chat sequence], ASC
    )
VAR _Result =
     MAXX( _Top, 'Table'[Created On] ) -
        MINX( _Top, 'Table'[Created On] )
RETURN 
    FORMAT ( INT ( _Result ), "0:" ) & FORMAT ( _Result, "hh:mm:ss" )
Rana80
Frequent Visitor

 Thank you! This works. Sorry the PBIX file is restrict from my companies firewall. 

Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RimvoN-qnp24ULMpr?e=O5Ee68

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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