Number of members to any date

[oliverk]

I have the following columns in the DB

Membership level, joinDate, ExitDate,  Name

Level1,           01.01.2014,   31.01.2015,    name1

Level2,           01.06.2014,   31.12.2014,    name2

Level2,           01.06.2014,   31.12.2014,    name3

Level3,           01.05.2014,   30.05.2016,    name4

Level1,           201.06.2016                        name 5

would like the following:

Thanks for Help

[v-huizhn-msft · Accepted solution]

Hi @oliverk,

As @Greg_Deckler said, create a Canlendar table by clicking "New Table" under Modeling on Home page.

Canlendar = CALENDAR(MIN('Table'[JionDate]),MAX('Table'[ExitDate]))

Create year and month calculated columns using the formulas.

Year = YEAR(Canlendar[Date])
Month = MONTH(Canlendar[Date])

Then create  a measure using the formula.

nember of people = CALCULATE(COUNTA('Table'[Name]),FILTER('Table','Table'[JionDate]<MAX(Canlendar[Date]) && 'Table'[ExitDate]>MAX(Canlendar[Date])))

Create two slicers including year and month respectively. Create a table visual to display the result.


More details, please refer to the attached file.

Thanks,
Angelia

[Greg_Deckler]

So, you probably need a disconnected Calendar table and then you would be able to do a measure that counts rows if the MAX of that date is greater than joinDate and less than ExitDate, maybe something like:

CALCULATE(COUNTA(Name),FILTER(Table,Table[joinDate]<MAX(Calendar[Date]) && Table[ExitDate]>MAX(Calendar[Date]))