Forum Discussion

KarlConstruct's avatar
KarlConstruct
Frequent Visitor
7 years ago
Solved

Calculating Average Time based on a Status

Hello,

 

I am struggling with a problem calculating average days in Power BI Desktop.

 

I have the following data in their own columns;

 

  • Status Column
  • Created date (date/hh/mm/ss)
  • Last Modifed date (date/hh/mm/ss)

 

My aim is to find a formula/s which will help me calculate;

 

  • The time interval (in days) spent between Date Created and Last Modified only if the status is set to "Passed". 

Does anyone know the steps, or formula's that i can use to calculate this information? 

 

Thanks!

Need Help
Tips and Tricks
  • jsh121988's avatar
    jsh121988
    7 years ago

    So in PowerBI there are 2 locations to create custom calculated columns:

    1. Through Query Editor using PowerQuery (M) language
    2. Though the main window using DAX language. This is similiar to excel.

    I gave you the DAX version because it's easier to read/understand/manipulate IMO. With DAX, you don't need to reload your data everytime there is logic changed on a caluclated column. Instead, DAX uses your existing loaded dataset, and processes the logic. This makes is much more agile than SQL or PowerQuery. However, I still write native SQL queries to pull my data, and even build calculated columns in SQL. I tend to avoid PowerQuery if possible, though it has it's benefits like parsing JSON.

     

    Going Forward:

    1. Delete that step in PowerQuery
    2. Load your data
    3. From the Home Tab, Add a Calculated Column
    4. Paste my formula
    5. View the data on the Data view on the left side of the main Pbi window

    Add Field

     

     

     

     

     

     

4 Replies

  • jsh121988's avatar
    jsh121988
    Microsoft Employee
    7 years ago

    This is fairly easy in DAX with a new calculated column.

     

    PassedDays =
    IF( [Status] = "Passed",
        DATEDIFF([Created], [LastModified], SECOND) / 60 / 60 / 24,
        BLANK()
    )
    // I intentionally do a datediff using seconds and divide because days rounds down and it's not an accurate representation.
    // This means a DATEDIFF('2019-01-01 23:59','2019-01-02 00:01', DAY) = 1 Day even though it's 2 minutes.

    Also, this MUST return BLANK() if not 'Passed' so the Non-Passed items don't get calculated in the average.

     

    • KarlConstruct's avatar
      KarlConstruct
      Frequent Visitor
      7 years ago

      Thank you for the info, when I plugged in this formula to a custom column, I am recieving errors.  Could you give me any pointers on what im doing wrong here?  (see screenshots below)

       

      Appreciate the help. 

       

      Formula entered into custom column

      error received

      • jsh121988's avatar
        jsh121988
        Microsoft Employee
        7 years ago

        So in PowerBI there are 2 locations to create custom calculated columns:

        1. Through Query Editor using PowerQuery (M) language
        2. Though the main window using DAX language. This is similiar to excel.

        I gave you the DAX version because it's easier to read/understand/manipulate IMO. With DAX, you don't need to reload your data everytime there is logic changed on a caluclated column. Instead, DAX uses your existing loaded dataset, and processes the logic. This makes is much more agile than SQL or PowerQuery. However, I still write native SQL queries to pull my data, and even build calculated columns in SQL. I tend to avoid PowerQuery if possible, though it has it's benefits like parsing JSON.

         

        Going Forward:

        1. Delete that step in PowerQuery
        2. Load your data
        3. From the Home Tab, Add a Calculated Column
        4. Paste my formula
        5. View the data on the Data view on the left side of the main Pbi window

        Add Field