Power BI: Half Year Time Intelligence

Power BI provides various time intelligence functions. But as of now, it does not include half-year time intelligence functions.

For this, we need to use custom time intelligence. We need to create the following columns in the calendar.

1. Create half years based on the year start date
2. Create Year Half and Half-year start date
3. Create rank Year Half
4. Create day of Half-year for Half Year Till Date

Standard Calendar will look like

Calendar = var _tab = ADDCOLUMNS( CALENDAR(date(2018,01,01), date(2023,12,31)), "Year Start Date" , date(year([date]),1,1)
, "Year End Date" , date(year([date]),12,31)
, "Month Start Date", EOMONTH([date],-1)+1
, "Month End Date", EOMONTH([date],0)
, "Half",  QUOTIENT( DATEDIFF(date(year([date]),1,1), EOMONTH([date],-1)+1, MONTH),6)+1
,"Year Half", year([date])*100 + QUOTIENT( DATEDIFF(date(year([date]),1,1), EOMONTH([date],-1)+1, MONTH),6)+1
, "Year", YEAR([date])
)
return
ADDCOLUMNS(_tab,
"Half Start Date", Minx(filter(_tab, [Year Half] = EARLIER([Year Half])),[Date])
,"Half Year Rank", RANKX(_tab,[Year Half],,ASC,Dense)
, "Day of Half", datediff(Minx(filter(_tab, [Year Half] = EARLIER([Year Half])),[Date]) ,[date],DAY)+1)

My data model has a sales table with measures like net, gross, margin joined with the date table on Sales date

Half-yearly measures

This Half Yr = CALCULATE([Net], FILTER(all('Calendar'), 'Calendar'[Half Year Rank] = max('Calendar'[Half Year Rank])))
Last Half Yr = CALCULATE([Net], FILTER(all('Calendar'), 'Calendar'[Half Year Rank] = max('Calendar'[Half Year Rank]) -1 ))

Trailing 6 Month measure

Tail 6 month = CALCULATE([Net], DATEADD('Calendar'[Date],-6,MONTH))

Half-year till date, Last Half-year till date, Last year same half

HYTD = CALCULATE([Net], FILTER(all('Calendar'), 'Calendar'[Half Year Rank] = max('Calendar'[Half Year Rank]) && 'Calendar'[Day of Half] <= max('Calendar'[Day of Half])))
LHYTD = CALCULATE([Net], FILTER(all('Calendar'), 'Calendar'[Half Year Rank] = max('Calendar'[Half Year Rank])-1 && 'Calendar'[Day of Half] <= max('Calendar'[Day of Half])))
LYHYTD = CALCULATE([Net], FILTER(all('Calendar'), 'Calendar'[Half Year Rank] = max('Calendar'[Half Year Rank])-2 && 'Calendar'[Day of Half] <= max('Calendar'[Day of Half])))

You can change the calendar based on a different FY

Calendar = var _tab = ADDCOLUMNS( CALENDAR(date(2018,02,01), date(2023,01,31))
, "Year Start Date" , if(month([date])<2,  date(year([date])-1,2,1),  date(year([date]),2,1))
, "Year End Date" , if(month([date])<2,  date(year([date]),1,31) , date(year([date])+1,1,31))
"Month Start Date", EOMONTH([date],-1)+1
, "Month End Date", EOMONTH([date],0)
, "Half",  QUOTIENT( DATEDIFF(if(month([date])<2,  date(year([date])-1,2,1),  date(year([date]),2,1)), EOMONTH([date],-1)+1, MONTH),6)+1
,"Year Half", year(if(month([date])<2,  date(year([date])-1,2,1),  date(year([date]),2,1)))*100 + QUOTIENT( DATEDIFF(if(month([date])<2,  date(year([date])-1,2,1),  date(year([date]),2,1)), EOMONTH([date],-1)+1, MONTH),6)+1
, "Year", YEAR([date])
)
return
ADDCOLUMNS(_tab,
"Half Start Date", Minx(filter(_tab, [Year Half] = EARLIER([Year Half])),[Date])
,"Half Year Rank", RANKX(_tab,[Year Half],,ASC,Dense)
, "Day of Half", datediff(Minx(filter(_tab, [Year Half] = EARLIER([Year Half])),[Date]) ,[date],DAY)+1
)

The same measures will continue to work. You can check the video for more details.

Want to Learn More, Explore these videos and video series.

If you want to learn Power BI from the ground up — covering everything from basic data modeling to advanced DAX and real-world analytics — be sure to explore my 20-hour Masterclass, packed with 150+ essential topics to elevate your data analytics and business intelligence skills.

Complete Power BI Tutorial/Course in Just 11 Hours

Mastering Microsoft Fabric: 30 Videos

Want to Learn SQL: 20 Videos

Mastering Power BI: 230+ Videos

Power BI Concepts- How Power BI Works- 150+ Videos

Migrating from Tableau to Power BI: 30 Videos

DAX for SQL Users: 10 Videos

What works in Power BI Direct Query- 10+ Videos

सीखे Power BI हिंदी में- 60+ Videos

Power BI Hindi On Demand- 10+ Videos

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share