Date Hierarchy Needed

[Anonymous]

Hi,

I need to create a Calculated Column using a date column by multiple & statements to create a unique key in the Fact Table. However I do not need the time portion of the date table. The problem is when I join the column with and the time portion goes with it.

A solution which I think will help if the date column was a date heirarchy, but I have that column coming from SQL. Is there a way I can create the heirarchy as I cannot use this function which is obvious as there is no heirarchy for the date to come from.

** PQ is not an option as I cannot do transformations since its a 'Direct Query'

Below is the output I need.

Below is what I have

Not sure if I was able to explain in the best way, but your help would go a long way! 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

You may try

New Column = DATEVALUE([DATE])

New Column = DATE(YEAR([DATE]),MONTH([DATE]),DAY([DATE]))

If your DATE column is Date/Time type, the new column will be Date/Time as well.

So that you could follow @Samarth_18 's suggestion to manually change the new column 's type to Date. Or go to Column Tools --> Change Data type:

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

[Samarth_18]

Hi @Anonymous ,

Since its a direct query you will not able to create a custom column so you can change your date column data type from date/time to date from the Model window like below:-

And for enabling date hierarchy you need to enable below option from option and settings:-

Thanks,

Samarth

[parry2k]

@Anonymous change the column type in the PQ to date instead of date/time and that will do it.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡