Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am struggling with a problem calculating average days in Power BI Desktop.
I have the following data in their own columns;
My aim is to find a formula/s which will help me calculate;
Does anyone know the steps, or formula's that i can use to calculate this information?
Thanks!
Solved! Go to Solution.
So in PowerBI there are 2 locations to create custom calculated columns:
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:
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.
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.
So in PowerBI there are 2 locations to create custom calculated columns:
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:
Thanks for the explaination, this helps a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |