Count number of rows that were active each month and add to summary table

[AndreaA4S]

I have two tables one with a row for each month of the year, the other is the table of project members with start dates and end dates. I need to add a column in the first table to count how many active project members there were each month.

I tried this:

# Members = COUNTAX('7 Expert Panel data',and(('7 Expert Panel data'[EOM Start]<='0 Summary table'[Month end]),(OR(ISBLANK([EOM End]),('7 Expert Panel data'[EOM End]>'0 Summary table'[Month end])))))

But to no avail! Please help 🙂

[johnbasha33 · Accepted solution]

@AndreaA4S 

1. Iterate through each row in the summary table.
2. For each row, count the number of project members from the project members table whose start date is before or equal to the current month end date in the summary table, and either have a blank end date or their end date is after the current month end date.

Here's how you can do it in Power BI using DAX:
Active Members =
VAR CurrentMonthEnd = '0 Summary table'[Month end]
RETURN
CALCULATE(
COUNTROWS('7 Expert Panel data'),
FILTER(
'7 Expert Panel data',
'7 Expert Panel data'[EOM Start] <= CurrentMonthEnd &&
(ISBLANK('7 Expert Panel data'[EOM End]) || '7 Expert Panel data'[EOM End] > CurrentMonthEnd)
)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[AndreaA4S]

Amazing thank you so much 🙂

[johnbasha33]

@AndreaA4S  glad that it worked out for you. Keep exploring this forum for other issues.  🙂

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Rupak_bi]

Hi,

The syntax should work. plz share sample table schema and the dax. alternetive is to use userrelationship function to create relationship with two date column.