topic Re: DAX for empty date column in DAX Commands and Tips

DAX for empty date column

Anonymous — Fri, 29 Apr 2022 09:11:18 GMT

Hi,

I have a data set with a date column (DD.MM.YYYY), a year column and a quarter column.

I want to primarily use the date column for time intelligence. However, some rows in the date column are empty. The year (i.e. 2022) and quarter (i.e. 1,2 etc) columns always have data.

Therefore, I want to link to year and quarter columns in the instance where the date columns are empty, to create an artifical date (i.e. 31.03.2022 for Q1, 2022) .

I was considering doing this via an IF Statement with multiple conditions? What do you suggest?

Thank you for your help!

Re: DAX for empty date column

SpartaBI — Fri, 29 Apr 2022 08:56:10 GMT

Anonymous
If you have blank rows in your date table than this is not a proper date table and you won't be able use time intelligence with it. You need to clean your data before you bring it to the model.

Check this for a good reference for a simple date table:
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Re: DAX for empty date column

v-yalanwu-msft — Tue, 03 May 2022 08:04:17 GMT

Hi, Anonymous ;

To create a new column, use:

column = IF ([Date] = blank (), the Date (2022,3,31), [Date])

If you have a lot of conditions, you can also use switch.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.