Dynamically Compute Different Time Duration in Power BI Using DAX

By:   |   Updated: 2021-12-01   |   Comments (2)   |   Related: > Power BI


Problem

Calculating time duration in Days, Hours, Minutes and Seconds in Microsoft Power BI using DAX is a common task most Analysts would undertake in their daily work with Power BI. Although the task is common, and there have been several articles written to demonstrate how to achieve this over the years, but I am putting together this detailed tutorial with an optional approach on how this can be easily done in a different business scenario by anyone using Power BI.

Solution

To demonstrate this, I will approach it from the two different business scenarios as follows.

  1. You have customer Order Dates and Ship Dates and want to know how long it took for the order to be delivered.
  2. You have a dataset which only has some fields along with a column of numbers representing total minutes staff worked in an hospital ward or something similar.

Scenario 1:

To approach the first scenario, let's say we have the dataset as seen in the diagram below.

Sample source data showing Orderdate and Shipdate

Then, the business requirement is for you to create a dynamic calculation in Power BI with a DAX formula that easily computes the Day, Hour, Minute and Seconds between Order Date and Ship Date of an order (i.e. date difference), and this date value should be output in a string format as seen below.

Calculated measure showing Duration in Days, Hours, Minutes and Seconds.

Or maybe it might be that this information needs to be seen on a table visual for business needs as seen below.

Diagram showing table visual with column for duration.

So, let's look at how we can achieve this using DAX. In this scenario, we are assuming we want to start with a situation where we have the duration in seconds. Note: You can start with Minutes or Hours too. However, I encourage you to start from the lowest time duration for ease of following this tutorial.

  1. Calculate the difference between the Order date and Ship date to know the number of days it took.
  2. Create a measure that holds the total duration in seconds.
  3. Create the measure that computes the string of the time duration.

STEP 1: Calculate the difference between the Order date and Ship date to know how long it took.

To do this, we need to create a calculated column using the DATEDIFF DAX function as seen in the diagram and DAX syntax below.

DAX code for time difference between two dates.
Time difference (Seconds) = DATEDIFF ( SalesData[OrderDate 2], SalesData[ShipDate 2], SECOND )

Note that I have used "SECOND" as my third argument in the DATEDIFF function. There are options to use other time durations like HOUR, MINUTES, etc.

STEP 2: Create a measure that holds the total duration in seconds.

This step is optional as it can be done within the third step. But I have decided to separate it for clarity's sake.

To do this, create a measure in DAX using a simple SUM function as seen in the diagram and code below.

DAX code for Sum of Duration in seconds.
Duration (Seconds) = SUM ( SalesData[Time difference (Seconds)] )

STEP 3: Create the measure that computes the string of the time duration.

I have created this measure using DAX variables as it makes it easier to understand and read. A combination of other DAX functions has been used in this measure; I will leave links to them in the next steps at the end of this article.

To do this, create a measure using DAX as seen in the diagram and code below.

Diagram showing DAX code for calculating a measure for Duration in Days, Hours, Minutes, and Seconds.
Duration (Days, Hours, Mins & Secs) - Measure = 
 
        // This retrieves the total duration in Seconds
        VAR _Seconds = [Duration (Seconds)]

        // This calculates the total working duration in Minutes
        VAR _Minutes = INT(_Seconds/60)
 
        // This computes the total minutes remaining 
        VAR _MinsLeft = MOD(_Minutes, 60)
 
        // This computes the total seconds remaining 
        VAR _SecsLeft = MOD(_Seconds,60)
 
        // This computes the total working duration in Hours
        VAR _Hours = INT(_Minutes/60)
 
        // This computes the total hours remaining 
        VAR _HrsLeft = MOD(_Hours, 24)
 
        // This computes the total working duration in Days
        VAR _Days = INT(_Hours/24)
 
        // This computes a string combination of the relevant dates to output the duration in Days, Hours, Minutes, and Seconds
        VAR _Result = _Days&" Days "
            &_HrsLeft&" "&"hrs "
            &_MinsLeft&" "&"Mins "
            &_SecsLeft&" "&"Secs"
    
    RETURN
        _Result

If you would like to copy the code directly into your own measure, all you need to do is change the "Duration (Seconds)" part at the top to your own calculated measure name, assuming it's also a second's time calculation.

The output of the DAX code above is as seen below. The DAX code can also work as a calculated column instead of a measure.

Diagram showing a card visual with duration measure calculation.

Scenario 2:

In this scenario, you have a dataset which only has some fields along with a column of numbers representing total minutes staff worked in an hospital ward or something similar. Take the dataset below as a representation for the scenario.

Sample data source table with column for Duration in minutes.

In this case, the business requirement could be that you need to create a DAX solution that creates a string of duration which depends on the "Staff Working Duration/Day (Mins)" to create something like the below.

Diagram showing a card visual with duration measure calculation in Days, Hours, and Minutes.

In this case, we must start from Minutes not Seconds as we did in the previous scenario. The steps are similar, nevertheless. To do this, we will follow the steps below.

  1. Create a measure that holds the total duration in minutes.
  2. Create the measure that computes the string of the time duration.

STEP 1: Create a measure that holds the total duration in Minutes.

Again, this step is optional as it can be done as part of the second step. This step is only separated from the second step to make it clearer to understand. So, to do this, all you need is to create a simple SUM function to create a measure that calculates the sum of the duration in Minutes as seen in the code and diagram below.

Total Minutes Duration = SUM ( 'Ad Data'[Staff Working Duration/ Day (Mins)] )
Measure calculation for Sum of duration in Minutes.

STEP 2: Create the measure that computes the string of the time duration.

Like what we did in the first scenario, we are using variables in the DAX measure for ease of readability. To do this, the DAX code should be as follows.

Diagram showing DAX code for calculating a measure for Duration in Days, Hours, and Minutes.
Duration in Days, Hours and Mins - Measure = 
 
        // This calculates the total working duration in minutes for all wards
        VAR _Minutes = [Total Minutes Duration]
 
        // This computes the total minutes remaining 
        VAR _MinsLeft = MOD(_Minutes, 60)
 
        // This computes the total working duration in hours for all wards
        VAR _Hours = INT(_Minutes/60)
 
        // This computes the total hours remaining 
        VAR _HrsLeft = MOD(_Hours, 24)
 
        // This computes the total working duration in Days for all wards
        VAR _Days = INT(_Hours/24)

        // This computes a string combination of the relevant dates to output the duration in Days, Hours, Minutes, and Seconds
        VAR _Result = _Days&" Days "
            &_HrsLeft&" "&"hrs "
            &_MinsLeft&" "&"Mins "
    
    RETURN
        _Result

Again, if you would like to reuse this code in your own work, all you need do is to just change the [Total Minutes Duration] part of the code to the name of your aggregated minutes measure.

The output of the measure in a card visual and a slicer is as seen below.

Diagram showing a card visual with duration measure calculation and a slicer.

In summary, as mentioned earlier in this article, this requirement in Power BI is not new, there have been many other articles that have demonstrated how to approach this. However, I have made this article very easy to follow using an approach which can be replicated by anyone.

Next Steps
  • See this YouTube video from BI Elite on using GIF in Power BI here.
  • Learn more about how to use the DAX INT function here.
  • Learn more about the DAX MOD function here.
  • Get some more information on how to use the DAX DATEDIFF function here.
  • Check out all of the Power BI tips on MSSQLTips.com.
  • Try this tip out with your data.



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips


Article Last Updated: 2021-12-01

Comments For This Article




Tuesday, March 29, 2022 - 5:32:46 PM - Aigbe Kenneth Omorodion Back To Top (89952)
Hi Sidharth,

Good to hear you have gained from this article.

I am happy to help with this, but i am not too clear as to what you want help with in particular. Are you saying you want to be able to display scenario 1 as a table visual?

Thanks

Tuesday, March 29, 2022 - 9:37:05 AM - Sidharth M Back To Top (89947)
Hi,
Thank you for sharing this wonderful article. can you please help me with the Scenario 1 total 1856 Days 17 hrs 3 Mins 44 Secs calculation in table?

what is the options / formula to shown that in table?

Regards,
Sidharth M


download














get free sql tips
agree to terms