Forum Discussion
Helper IIRolling 24 months headcount
Hi,
I have a requirement to show the rolling 24 month Headcount by month based on selected date. I was able to get the end of month HC but strugling to get the 24 month HC by each month. Please can you help me with the Dax measure for the rolling 24 month HC.
my measure for end of month HC is below:
Hi DarshanKumar
Thank you for reaching out to the Microsoft Fabric Forum Community.
Can you please try below points.The issue is that your slicer is filtering the whole visual to just one month. So even though your DAX is trying to calculate a 24-month range, the chart only sees that one selected month.
you can create a disconnected date table just for the slicer. Something like:Slicer_Month =
ADDCOLUMNS (
CALENDAR ( DATE(2020, 1, 1), TODAY() ),
"MonthYear", FORMAT ( [Date], "MMM YY" )
)
Use this new Slicer Month table in your slicer (not the regular calendar table). then update your measure to read the selected date from this slicer and not let it filter the visual.
That way, the slicer sets the end date for the rolling window, but your chart still shows all the 24 months properly
If this information is helpful, please “Accept as solution” to assist other community members in resolving similar issues more efficiently.
Thank youThanks Anonymous this works. Appologies for the delayed response.
7 Replies
- andrewsommer
Super User
Your current Month End Headcount measure works for a single month, but to build the rolling 24-month trend, you need a DAX measure that:
Dynamically calculates EOM headcount for each month in the 24-month window, and
Displays it correctly on a line or matrix visual over time.
Rolling 24M Headcount = VAR SelectedDate = MAX('dim_calendar'[Date]) VAR StartDate = EOMONTH(SelectedDate, -23) -- 23 months before selected month RETURN CALCULATE( [Month End Headcount], FILTER( ALL('dim_calendar'[Date]), 'dim_calendar'[Date] >= StartDate && 'dim_calendar'[Date] <= SelectedDate && DAY('dim_calendar'[Date]) = DAY(EOMONTH('dim_calendar'[Date], 0)) -- ensure EOM dates only ) )Please mark this post as a solution if it helps you. Appreciate Kudos.
DarshanKumarThanks @@andrewsomme but whn i display the data on a line chart it only shows the selected month HC. Not sure what im missing, please can you help.
Hi DarshanKumar
Thank you for reaching out to the Microsoft Fabric Forum Community.
Can you please try below points.The issue is that your slicer is filtering the whole visual to just one month. So even though your DAX is trying to calculate a 24-month range, the chart only sees that one selected month.
you can create a disconnected date table just for the slicer. Something like:Slicer_Month =
ADDCOLUMNS (
CALENDAR ( DATE(2020, 1, 1), TODAY() ),
"MonthYear", FORMAT ( [Date], "MMM YY" )
)
Use this new Slicer Month table in your slicer (not the regular calendar table). then update your measure to read the selected date from this slicer and not let it filter the visual.
That way, the slicer sets the end date for the rolling window, but your chart still shows all the 24 months properly
If this information is helpful, please “Accept as solution” to assist other community members in resolving similar issues more efficiently.
Thank you
- Rupak_bi
Hi DarshanKumar ,
When you select a month in the slicer, the table is getting filtered to the month. thats why its showing the selected month only in the chart. to get last 24 month rolling, the measure and the modelling to be modified. please share sample data to work on this.
Hi DarshanKumar
Thank you for reaching out microsoft fabric community forum.
Could you please share some sample pbix .so that we can assist you more efficently.
Thank you.Hi DarshanKumar
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
- DarshanKumar
Thanks Anonymous this works. Appologies for the delayed response.
