Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 slicer.

 

Emp ID Start             End              S      Pos   Name     Max ST Date Act   Inact      JoinDate     LeaveDate

110/01/201110/01/201501001Pepe11/01/20150010/01/2011 
111/01/201501/01/999931002Pepe11/01/20151010/01/2011 
203/05/200003/07/200901002Pedro28/10/20160003/05/2000 
204/07/200927/10/20160321Pedro28/10/20160003/05/2000 
228/10/201601/01/999931001Pedro28/10/20161003/05/2000 
303/06/201901/01/999931001Daniel03/06/20191003/06/2019 
407/11/201107/11/20130298Maria07/11/20110107/11/201107/11/2013
509/03/201609/05/201601001Marta10/05/20180009/03/2016 
510/05/201801/01/99993321Marta10/05/20181009/03/2016 

 

And the dax formula:

RollingActive =
   CALCULATE (
   DISTINCTCOUNT ( Sheet1[Emp ID] );
   FILTER ( ALL ( Sheet1[JoinDate] ); Sheet1[JoinDate] <= [selected date] )
)
 
 
However, it is not returning any value. The spected output in this case should be 3 (as Emp ID 1,2 and 4 were active in that selected date)
What I'm doing wrong?
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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
    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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
    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.