Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
oliverk
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:

Ergebniss.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks for Help

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
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]))

1.PNG

Create year and month calculated columns using the formulas.

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

3.png

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.

5.PNG
More details, please refer to the attached file.

Thanks,
Angelia


View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
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]))

1.PNG

Create year and month calculated columns using the formulas.

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

3.png

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.

5.PNG
More details, please refer to the attached file.

Thanks,
Angelia


Greg_Deckler
Super User
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]))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.