Data difference in the same column including hours

[akhil148265]

Hi,

My date and time present in the same column, i wanted the difference of date and hours in the power bi.

I have explained in the excel file, need to achieve the 'Time Taken' field in the poer bi ( The logic field is the requirement).

Thanks in advance.😊 

Regards,

Akhil.

[v-gizhi-msft]

Hi,

According to your description, I create a table to test:

I have two ways to calculate Time Taken by column and measure:

Please take following steps:

By column:

1)Create a column to get original time’s last time:

Last Date Time - Column =

CALCULATE(MAX('Test'[Status Date Time]),FILTER('Test','Test'[Status Date Time] < EARLIER('Test'[Status Date Time]) && 'Test'[WorkitemID] = EARLIER('Test'[WorkitemID])))

2)Create a column to calculate Time Taken:

Time Taken - Column = DATEDIFF(Test[Last Date Time - Column],Test[Status Date Time],SECOND)/(3600*24)

By measure:

1)Create a measure to get original time’s last time:

Last Date Time - Measure =

CALCULATE(MAX('Test'[Status Date Time]),FILTER(ALLSELECTED('Test'),'Test'[Status Date Time] < CALCULATE(MIN('Test'[Status Date Time]),'Test') && 'Test'[WorkitemID] in FILTERS('Test'[WorkitemID])))

2)Create a measure to calculate Time Taken:

Time Taken - Measure = DATEDIFF([Last Date Time - Measure],MIN('Test'[Status Date Time]),SECOND)/(3600*24)

And the above two ways’ results show:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[Mariusz · Accepted solution]

Hi @akhil148265 

You can add calculated column like below.

Column = 
VAR __current = 'Table'[Status Date Time]
VAR __previous = CALCULATE(
    MAX( 'Table'[Status Date Time] ),
    ALLEXCEPT( 'Table', 'Table'[WorkItemId] ),
    'Table'[Status Date Time] < __current 
)
VAR __weekends = 
    COUNTROWS(
        FILTER(
            CALENDAR( 
                IF( NOT ISBLANK( __previous ), __previous,  __current ), 
                __current ),
            WEEKDAY( [Date], 2 ) IN { 6, 7 } ) 
    ) - 2
    
RETURN IF( 
    NOT ISBLANK( __previous ), 
    __current - __previous - __weekends 
)

Also, see the attached file for reference.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[akhil148265]

Hi @Mariusz,

The logic which you have provided works for the different dates, but if the status changes at the same 'date and time' the result gives incorrectly. Could you please help me with this😊.

Attached is the screnario ( Raw Data).

Regards,

Akhil.

[amitchandak]

Please refer

https://community.powerbi.com/t5/Desktop/DATEDIFF-with-EARLIER-Function/td-p/559505

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601