topic Count number of rows that were active each month and add to summary table in DAX Commands and Tips

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

AndreaA4S — Tue, 09 Apr 2024 08:30:02 GMT

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 🙂

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

Rupak_bi — Tue, 09 Apr 2024 14:15:48 GMT

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.

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

johnbasha33 — Wed, 10 Apr 2024 11:04:35 GMT

AndreaA4S

Iterate through each row in the summary table.
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 !!

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

AndreaA4S — Thu, 11 Apr 2024 07:41:51 GMT

Amazing thank you so much 🙂

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

johnbasha33 — Thu, 11 Apr 2024 14:42:57 GMT

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 !!