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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Number of active members in matrix based on entry/exit date

Hey guys

I am trying to create a report with a matrix that shows the number of entries, exits and total number of members active members based on date. After spending hours and hours on searching for and attempting different methods, I cannot get the total to work properly.

The data is in multiple tables:

Date table is connected to entry date and exit date in respective tables. And personnel table is connected to the name of entry/exit table. Exit date is only present on members that left.

The goal would be to have a third column showing total number of active members for each year/month. Preferably to also make it so can be sorted by department as shown in the first/left matrix.

Does anyone know how to do this? Or has a different approach achieving similar result?

I attached a .pbix with the sample data from the screenshots.
https://www.dropbox.com/s/m2u9zxplefx8ap4/Active%20Members%20Matrix.pbix?dl=0

Thank you!

1 ACCEPTED SOLUTION
Community Support

Hi @Stax ,

Please check the model.

``Count of Austritt = COUNT('Austritt'[Austritt])``
``Count of Eintritt = COUNT('Eintritt'[Eintritt])``
``````total =
VAR _date =
MAX ( 'Date'[Date] )
VAR _table =
SUMMARIZECOLUMNS (
'Personal'[Departement],
'Date'[Year],
'Date'[Month],
'Date'[Date]
)
VAR _total =
CALCULATE ( [Count of Eintritt], 'Date'[Date] <= _date )
- CALCULATE ( [Count of Austritt], 'Date'[Date] <= _date )
RETURN
_total``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 REPLIES 2
Community Support

Hi @Stax ,

Please check the model.

``Count of Austritt = COUNT('Austritt'[Austritt])``
``Count of Eintritt = COUNT('Eintritt'[Eintritt])``
``````total =
VAR _date =
MAX ( 'Date'[Date] )
VAR _table =
SUMMARIZECOLUMNS (
'Personal'[Departement],
'Date'[Year],
'Date'[Month],
'Date'[Date]
)
VAR _total =
CALCULATE ( [Count of Eintritt], 'Date'[Date] <= _date )
- CALCULATE ( [Count of Austritt], 'Date'[Date] <= _date )
RETURN
_total``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Frequent Visitor

Hey @v-cgao-msft ,

That’s perfect, thanks!
I used this to build what I needed.

But I’m a bit confused - why does that work? I attempted to do something similar but couldn’t really find a way to iterate through the dates in date table.
It seems like “MAX(‘Date’[Date]) does exactly that. But why does it do that? I would’ve expected it to just return the last date in the table.
Or am I misunderstanding the whole thing completely?

Thanks again.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors