cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

New Member

## Number of members to any date

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

1 ACCEPTED SOLUTION
Employee

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

2 REPLIES 2
Employee

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

Super User

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]))`

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...