Forum Discussion

Asil098's avatar
Asil098
New Member
1 year ago
Solved

calculating time

Hi Everyone, I am trying to calculate the time difference between the end_date and the start_date, and I want the result in minutes. In some observations, start_date> end_date, and I have used the i...
Need Help
  • v-tejrama's avatar
    v-tejrama
    1 year ago

    Hi Asil098,

     

    Thanks for sharing your query in detail.

     

    I have tested and reproduced your scenario in Power BI, including cases where the start_date is greater than the end_date, and I’m happy to confirm that I got the expected output exactly as per your requirement.


    I used a DAX calculated column to compute the time difference in minutes, and it successfully handles both normal and reverse date situations. The output displays positive, negative, or zero values depending on the date order, matching your requirement perfectly.

    For your convenience, I have also attached a sample .pbix file showing the complete setup with data, DAX logic, and a table visual, so you can easily review, test, or modify it as needed.


    Please find the attached .pbix file for your reference.

     

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.


    Best Regards,
    Tejaswi.
    Community Support

     

     

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hey Asil098 ,

A few points to clarify:

  1. Power Query vs. Data Model (DAX): If you created the calculated column using DAX in the data model, it will not be visible in Power Query, as Power Query and the data model are separate layers. To use the result in Power Query, you need to re-create the logic within Power Query using M language.

  2. Handling start_date > end_date: In Power Query, you can add a custom column with logic like this to calculate the time difference in minutes safely:

    if [start_date] <= [end_date] then
    Duration.TotalMinutes([end_date] - [start_date])
else
    Duration.TotalMinutes([start_date] - [end_date]) * -1

    Or if you want to ignore negatives:

    Number.Abs(Duration.TotalMinutes([end_date] - [start_date]))

  3. DAX alternative for visuals or modeling: If you’re okay with using it in visuals instead of Power Query, your original DATEDIFF(start_date, end_date, MINUTE) DAX formula is correct. Just ensure you're using it in a measure or calculated column, and it’s being used in the correct context.

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam