Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
84 | |
76 | |
64 |
User | Count |
---|---|
145 | |
109 | |
109 | |
102 | |
96 |