Calculating Average Time based on a Status

[KarlConstruct]

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!

[jsh121988]

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]

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 · Accepted solution]

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

[KarlConstruct]

Thanks for the explaination, this helps a lot!