Forum Discussion
Calculate Rolling active members
Hi experts, I'm new to Power BI annd DAX, but I'm struggling with a simple calculation. I am trying to calculate the rolling active members for a HR Analysis, depending on selected date of a slic...
Hi Anonymous
Create a calendar table
calendar = FILTER(ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",FORMAT([Date],"mmmm")),DAY([Date])=1)
Don't create any relationship between this calendar table and other table
Create measures in Sheet5
selected = SELECTEDVALUE('calendar'[Date]) RollingActive = CALCULATE(DISTINCTCOUNT(Sheet5[Emp ID]),FILTER(Sheet5,Sheet5[JoinDate]<=[selected]))Add [year],[month] from "calendar" table to the slicer
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft
Community Support
Community SupportHi Anonymous
Create a calendar table
calendar = FILTER(ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",FORMAT([Date],"mmmm")),DAY([Date])=1)
Don't create any relationship between this calendar table and other table
Create measures in Sheet5
selected = SELECTEDVALUE('calendar'[Date])
RollingActive = CALCULATE(DISTINCTCOUNT(Sheet5[Emp ID]),FILTER(Sheet5,Sheet5[JoinDate]<=[selected]))
Add [year],[month] from "calendar" table to the slicer
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.