How to add Half year date column in date hierarchy (continuous date type)

[irenelitw629]

Hi,

How can I add "Half Year" to the date hierarchy? I want to enable the trend line so it needs to be continous date type.

Ideally the trend line chart can drill down to Year. Half Year, Quarter 3 different hierarchies

I tried to create a column using below DAX but it is text type and my trend line disapear...

Half Year=YEAR([Date])&" HY "&ROUNDUP(MONTH([Date])/6,0)

Thanks!

[Anonymous · Accepted solution]

Hi  @irenelitw629 ,

Here are the steps you can follow：

1. Create calculated column.

Year =
 YEAR('Table'[Date])

Half Year =
YEAR([Date])&"HY"&ROUNDUP(MONTH([Date])/6,0)

Quarter =
"Qtr"&" "&QUARTER('Table'[Date])

Month =
 FORMAT('Table'[Date],"mmmm")

Day =
 DAY('Table'[Date])

2. Check [Date] - click on the three ellipses - Create hierarchy.

3. Add [Year], [Half Year], [Quarter], [Month], [Day] in a customized order - to the created date hierarchy.

4. Result:

Create a slicer with a date hierarchy, and a Visual with the [Date] column as the primary, so that the slicer can hierarchize the Visual

Best Regards,

Liu Yang

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

[irenelitw629]

@Anonymous 

Thanks for your reply. I tried above but still can't enable trend line. Can you send me the file?

[irenelitw629]

@amitchandak , do you know the solution?