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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vishnuprashanth
Helper III
Helper III

How to create Half Year in the date hierarchy

Hi Everyone, 

How can I add "Half Year" to the date hierarchy? 

Adding Half year to date hierarchyAdding Half year to date hierarchy

 

Any help would be highly appreciated. TIA 

2 ACCEPTED SOLUTIONS
Floriankx
Solution Sage
Solution Sage

Hello you can add a calculated column

 

=YEAR([Date])&" HY "&ROUNDUP(MONTH([Date])/6)

View solution in original post

Thanks, @Floriankx

I have added this custom column and also a custom date hierarchy adding this column to it. 

Just a small correction in your code, need to add 0 after the 6 for ROUNDUP.

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


I create the hierarchy as shown in the solution of 
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-at-Hourly-Level/td-p/210538?lightbox-message....

Thanks again @Floriankx

View solution in original post

5 REPLIES 5
imtien2311
New Member

Hi team

 

I got an error for the operator "&" when I put the solution in my PowerQuery:

imtien2311_0-1676951302511.png

 

imtien2311_1-1676951327697.png

Any ideas how I can work around it? Thaaaaaanks

 

 

AnalystPower
Helper I
Helper I

Here is an alternative to the calculation meathod. @vishnuprashanth @iamprajot @Floriankx 

 

Right click on you Date column... even if it is the full date  and then select new group.

 

Then Bin it by 6 Months 

 
 
 

2020-06-12_11-30-22.jpg

iamprajot
Responsive Resident
Responsive Resident

In Visual Level Filters select what months are required.
Floriankx
Solution Sage
Solution Sage

Hello you can add a calculated column

 

=YEAR([Date])&" HY "&ROUNDUP(MONTH([Date])/6)

Thanks, @Floriankx

I have added this custom column and also a custom date hierarchy adding this column to it. 

Just a small correction in your code, need to add 0 after the 6 for ROUNDUP.

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


I create the hierarchy as shown in the solution of 
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-at-Hourly-Level/td-p/210538?lightbox-message....

Thanks again @Floriankx

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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