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

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

Stax_2-1679666504520.png

 

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.

Stax_3-1679666504527.png

 

 

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
v-cgao-msft
Community Support
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

vcgaomsft_0-1679894783878.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
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

vcgaomsft_0-1679894783878.png

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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