How to format date within dax expressions

DAX DAX Calculations
1

I’m trying to create a calculated column which calculates the difference between two dates in two date columns (beginning and end of a task assigned to a user). The hang up is that, if the date in one of the columns reads “1/1/1900 12:00:00 AM”, I want the result to be a blank value. The system automatically displays this date when the task isn’t complete.

Here’s what I have so far (which is producing an error)

Days to Complete Task = IF(DQ_VW_Tasks[closed_at] = “1/1/1900 12:00:00 AM”, Blank(), DATEDIFF(DQ_VW_Tasks[sys_opened_on], DQ_VW_Tasks[closed_at], DAY) )

Here’s a screenshot of the column with the date I’m trying to filter out of the results.

image

I believe the issue is that my if statement is trying to capture a text value, when I need to capture a date value. However, I don’t know how to format the date value in the if statement. I think it’s an easy fix.

Thank you!

@Harsh Any ideas on this one?

2

Hi Pete,
You can change the DQ_VW_Tasks[closed_at] value to text and then compare or you can extract date , month,year,hour,minute and second part separatelty and check it with logical condition.
Please check FORMAT function is working for your requirement.
FORMAT(DQ_VW_Tasks[closed_at],1)

Thanks

3

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

Can you please upload the working of the PBIX file for the reference? So that members of our forum can assist you in a better and efficient manner.

Thanks and Warm Regards,
Harsh

4

@pete.langlois Dates are integers starting from 12/30/1899.

image

1 Like
5

Hi there,

You should use Format Function.

Please mention the following link for your further consideration:

6

Hi @pete.langlois, did the response provided by the users and experts helped you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

7

Thanks @AntrikshSharma, how could I use this concept in my Dax to calculate a date diff?

I’m still not sure how to incorporate this. Thank you!

8

Thanks @IlgarZarbaliyev!

I’m fuzzy on how to use the format function in this function. Could you provide an example
On how this would work in a date diff calc using the column formatting in my initial post?

9

If you want to change the format of Date, you may do it using Format function in different ways:

For instance,
General Date = FORMAT(‘Date’[Date], “General Date”)
You may give some formats instead of “General Date”. Short Date, dd-mmm-yyyy, etc. can be used.

image
image1920×1080 295 KB

P.S.

10

Hi @pete.langlois, did the response provided by @IlgarZarbaliyev help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

11

Hi @pete.langlois , we’ve noticed that no response has been received from you since the 5th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

12

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!