Date issue

[Kasiop]

Hi,
 
Here is my issue:
I am trying to get the difference between two dates (I am using a direct query view), but on PBI desktop, I don’t see my fields as date format:

If anyone has any idea on why I cannot use the dateDiff or why I cannot see the date format for my two fields I will be more than happy.

thanks,

Naïma

[v-xicai]

Hi @Kasiop ,

You may select on the column [Closure Date], go to Column tools->Data type, change the data type to Date, do the same with column [Creation Date]. Then you may create measure instead of calculated column like DAX below.

Measure= DATEDIFF(MAX(Table1[Creation Date]),MAX(Table1[Closure Date]),DAY)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kasiop]

Thanks Amy, but it is "kind of" a format date already but I still cannot use the Dax measure: 

[v-xicai]

Hi @Kasiop ,

Based on my test which connect to SQL Server database using DirectQuery Mode in the latest version of Power BI Desktop, the DATEDIFF function works well.

DateDiff = DATEDIFF(MAX(Sales[CreationDate]),MAX(Sales[ClosureDate]),DAY)

>>Thanks Amy, but it is "kind of" a format date already but I still cannot use the Dax measure: 

Is there any error message to appear when you create this measure?

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Kasiop - What happens if you do this:

Measure = (MAX([Closure Date]) - MAX([Creation Date])) * 1.

?

[Kasiop]

Thanks, it returns -40 does it mean I use this formula?

[Kasiop]

Hi,

after applying the formul, i am not sure the outcome is right... if you have another idea... that will be great.

Thanks