Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
irenelitw629
Helper II
Helper II

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

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

2023-07-31_14-15-50.png

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vyangliumsft_0-1690963470679.png

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

vyangliumsft_1-1690963470683.png

vyangliumsft_2-1690963528972.png

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

 

vyangliumsft_3-1690963528994.png

 

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

vyangliumsft_0-1690963470679.png

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

vyangliumsft_1-1690963470683.png

vyangliumsft_2-1690963528972.png

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

 

vyangliumsft_3-1690963528994.png

 

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

@Anonymous 

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

irenelitw629
Helper II
Helper II

@amitchandak , do you know the solution?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors