Forum Discussion
Taking Date difference using Power Query or DAX
I am working on building a dashboard which takes data from a view using backend as snowflake. I can't make much changes to backend due to limited access.
I am facing issue while computing date difference if I use power query using the fomula below
Duration.Minutes(start_date - end_date)
The problem with above approach is it in not compatiable with DirectQuery mode.
Next, I tried creating a column using formula below
DATEDIFF(start_date, end_date, MINUTE)
This leads to the following:
We couldn't fold the expression to the data source. Please try a simpler expression.
How can can I take the date difference in minute given limited access to backend?
Hi, fhashim
well, you can first try something like this, instead of DATEDIFF:([Date A]-[Date B])*24or you can check the following blog posts:
https://community.powerbi.com/t5/Desktop/DATEDIFF-in-Direct-Query-Mode-32-bit/m-p/492878
https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397
4 Replies
Hi, fhashim
Is your source a SQL Database? If so, is the query (SQL) specific enough?
If you write a SQL query, write it as detailed as possible (take specific columns, tables etc).
If you write something like this: Select * from * , it's not really optimized and you can face those issues.
If you can't write it yourself, please refer to your IT department.Hi vojtechsima,
I can't do it myself so I might have to contact IT support but I just need to be sure that there isn't much that I can do right?Hi, fhashim
well, you can first try something like this, instead of DATEDIFF:([Date A]-[Date B])*24or you can check the following blog posts:
https://community.powerbi.com/t5/Desktop/DATEDIFF-in-Direct-Query-Mode-32-bit/m-p/492878
https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397
Hi fhashim
I just want to confirm if you resolved this question? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.
If you need more help, please let me know.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
