Unlinked Calendar and DAX

Question

Hello&nbsp;I am working on a HR PowerBImodel and as I am dealing with several date columns, a decision was taken to have an unlinked calendar table and have DAX account for any date selections via the date slicer. The unlinked Calendar is called dimdateselection and there are a series of column values under this table, the main one being [Date] and then we have other columns that state what period this date value is against our financial calendar eg all date from 01/12/2025 to 28/12/2025 is P8 for us.&nbsp;&nbsp;&nbsp;I'm first of all calculating daily headcount via the below measure which i can confirm is working correctly in terms of daily output:&nbsp;Headcount between Dates (Live) =VAR SelectedDate =&nbsp; &nbsp; MAX ( dimdateselection[date] )RETURNCALCULATE (&nbsp; &nbsp; DISTINCTCOUNT ( fctemployeerecord[foreignkeypeople] ),&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; fctemployeerecord,&nbsp; &nbsp; &nbsp; &nbsp; fctemployeerecord[RoleDateFrom] &lt;= SelectedDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; ( ISBLANK ( fctemployeerecord[RoleDateTo] ) || fctemployeerecord[RoleDateTo] &gt;= SelectedDate )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; VAR TrueLeaveDate = RELATED ( dimhrpeople[DateOfLeaving] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RETURN ISBLANK ( TrueLeaveDate ) || TrueLeaveDate &gt;= SelectedDate&nbsp; &nbsp; ))&nbsp;&nbsp;i then have the below measure that is calculating the % difference in average headcount rolling 12:&nbsp;&nbsp;Average Headcount Rolling 12 % change (Live) =VAR CurrentOffset = SELECTEDVALUE( dimdateselection[FiscalPeriodOffSet] )VAR WinCurr =&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; ALL ( dimdateselection[FiscalPeriodOffSet], dimdateselection[date] ),&nbsp; &nbsp; &nbsp; &nbsp; dimdateselection[FiscalPeriodOffSet] &gt;= CurrentOffset - 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; dimdateselection[FiscalPeriodOffSet] &lt;= CurrentOffset&nbsp; &nbsp; )VAR CurrRolling =&nbsp; &nbsp; AVERAGEX ( WinCurr, [Headcount between Dates (Live)] )VAR WinPrior =&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; ALL ( dimdateselection[FiscalPeriodOffSet], dimdateselection[date] ),&nbsp; &nbsp; &nbsp; &nbsp; dimdateselection[FiscalPeriodOffSet] &gt;= CurrentOffset - 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; dimdateselection[FiscalPeriodOffSet] &lt;= CurrentOffset - 11&nbsp; &nbsp; )VAR PriorRolling =&nbsp; &nbsp; AVERAGEX ( WinPrior, [Headcount between Dates (Live)] )RETURNIF (&nbsp; &nbsp; OR ( ISBLANK ( CurrRolling ), ISBLANK ( PriorRolling )),&nbsp; &nbsp; BLANK (),&nbsp; &nbsp; DIVIDE ( CurrRolling - PriorRolling, PriorRolling ))&nbsp;&nbsp;To explain some of the fields:Fiscalperiodoffset = a column in the date table that states dynamically if the date is the current fiscal period, a previous one or a future one. Our engineering team designed this for us that works well. In my P8 explaination above, p8 would be classed as fiscalperiodoffset = -1 as its the previous fiscal month. Currently any date beween 29/12/2025 - 25/01/2026 would be fiscalperiodoffset = 0 as its the current period.&nbsp;as you can tell, i had to get co pilot to help me write the dax (very new it all!) and the issue i am facing is that if i make a line graph visual where the Y axis is the measure&nbsp;Average Headcount Rolling 12 % change (Live) and the x axis is fiscalperiodoffset it populates correctly however the x axis will display 0, -1, -2 etc. Its not user friendly. I thought that by adding in another X axis value from the same dimdateselection table such as FiscalPeriodYear which would be eg 009.2026 for the current fiscal period, or 008.2026 for the previous fiscalperiod it would work. but the measure&nbsp;Average Headcount Rolling 12 % change (Live) doesn't seem to like this and returns blank. I think copilot when helping me with the measure boxed me into using that specific field through&nbsp;VAR CurrentOffset = SELECTEDVALUE( dimdateselection[FiscalPeriodOffSet] ) but i cannot seem to get this to work.&nbsp;what i think is important is that when i use dimdateselection[date] i get the output still correct just daily. whilst i can use this it means my visual looks a bit off as there are numerous date points meaning there are steps in my line graph.&nbsp;&nbsp;can anyone help me fix the measure&nbsp;Average Headcount Rolling 12 % change (Live) such that it lets me use any value from the unlinked calendar table which i know for each date has the associated value of fiscalperiodoffset and fiscalperiodyear correct against each date?

beabf · Accepted Answer

JHJHJH1988&nbsp;Hi!
Can you share the pbix to fix directly the measure on your data?
&nbsp;
Try to fix the measure as:
Average Headcount Rolling 12 % change (Live) =VAR CurrentOffset =MAX ( dimdateselection[FiscalPeriodOffSet] )
VAR WinCurr =FILTER (ALL ( dimdateselection ),dimdateselection[FiscalPeriodOffSet] &gt;= CurrentOffset - 11&amp;&amp; dimdateselection[FiscalPeriodOffSet] &lt;= CurrentOffset)
VAR CurrRolling =AVERAGEX ( WinCurr, [Headcount between Dates (Live)] )
VAR WinPrior =FILTER (ALL ( dimdateselection ),dimdateselection[FiscalPeriodOffSet] &gt;= CurrentOffset - 22&amp;&amp; dimdateselection[FiscalPeriodOffSet] &lt;= CurrentOffset - 11)
VAR PriorRolling =AVERAGEX ( WinPrior, [Headcount between Dates (Live)] )
RETURNIF (OR ( ISBLANK ( CurrRolling ), ISBLANK ( PriorRolling ) ),BLANK (),DIVIDE ( CurrRolling - PriorRolling, PriorRolling ))
&nbsp;
BBF
💡 Did I answer your question? Mark my post as a solution! 👍 Kudos are appreciated 🔥 Proud to be a Super User!

Forum Discussion

Unlinked Calendar and DAX

2 Replies

Related content

Average headcount DAX and unlinked calendar

Unlink Microsoft Onelake

Calendar Slicer with Different Business Calendars

Custom calendar

Tweaking Financial Calendar