Taking Date difference using Power Query or DAX

[fhashim]

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?

[v-xiaotang]

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.

[fhashim]

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?

[vojtechsima · Accepted solution]

Hi, @fhashim 
well, you can first try something like this, instead of DATEDIFF:

([Date A]-[Date B])*24

or 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-Differ...

[vojtechsima]

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.