How to Remove a single date from a date column

DAX DAX Calculations
1

Hello,

I have a calculated date column with date+time values. I changed the data type to short date so the time component is no longer there.

I would like create a new column that contains the same values as the original column, but I need to filter out all records that have the value 05/29/2021. I’m having trouble figuring out how to format the date component of the dax for the calculated column.

Currently, I’m using the following dax:

new date column = IF(DimLeads[CreatedDate] = date(2021,05,29), BLANK(), DimLeads[CreateDate])

The current result is a new column which contains the date+time values, even though I changed the data time in the original column to be short date.

Can anyone explain how to write dax to do this?

Thank you!
Pete

2

@pete.langlois ,

I think the better way to handle this is directly in Power Query. If you want to retain the time, then split the datetime column by delimiter into separate date and time fields. If you don’t need to retain the time, you can just click on Date and then select Date Only, per the screenshot below:

image
image555×1002 30.4 KB

Now once you have the date only, you can create a custom column simply to filter out the date you don’t want as such:

image
image2098×1314 146 KB

and Voila!

image
image990×1146 65.4 KB

You can now choose to retain the original Date column or not, depending on your needs.

I hope this is helpful.

  • Brian
2 Likes
3

Hello @pete.langlois, just following up if the response from @BrianJ help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Yes, his response was the solution, thanks @BrianJ