Calculate Enrollment For Each Date - DAX Measure

[rachaellearns]

Hello, 

I am new to PowerBI and need some help. 

I am looking to calculate and graph the number of club members on each date of the year. 

The data is structured like this:

memberid	entrydate	withdrawaldate
1	08/01/2020	09/07/2020
2	09/19/2020	10/01/2020
3	08/17/2020	NULL
4	08/05/2020	10/15/2020
5	10/01/2020	NULL
6	09/07/2020	10/22/2020
7	08/30/2020	10/07/2020
8	09/01/2020	10/25/2020
9	09/15/2020	NULL

I want to dynamically calculate the enrollment/membership counts for each day of the year. So for each day of a year, I want to count the members whose entry date is on or before that date AND whose withdrawal date is either after that date, or who have not withdrawn/have a null value in the withdrawaldate column. As the year progresses, new data and dates will need to be added to the graphs. This is what I have so far, but I am getting stuck on the filter portion. 

CALCULATE(

DISTINCTCOUNT('Enrollment'[memberid]),

FILTER(

)

)

[v-deddai1-msft · Accepted solution]

Hi @rachaellearns ,

I suggest you create a calendar table:

Date = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

Then use the following measure to calculate the enrollment/membership counts for each day of the year.

enrollment/membership counts = CALCULATE(COUNT('Table'[memberid]),FILTER(ALL('Table'),'Table'[entrydate]<=MAX('Date'[Date])&&IF(ISBLANK('Table'[withdrawaldate]),1,'Table'[withdrawaldate]>=MAX('Date'[Date]))))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[smpa01]

Hi,

Can you please give this a try and let me know if you like this.

Thanks.

[smpa01]

Please ignore my previous upload as the data had all non-null rows.

The one I am uplaoding now has rows with null values and work as per your specs.

[sevenhills]

DateSlicerCounts = CALCULATE(
    DISTINCTCOUNT(Table1EntryTable[MemberID]),
    FILTER(Table1EntryTable,
        Table1EntryTable[EntryDate] <= CALCULATE( MAX( DateTable[Date])) 
        && 
        IF(ISBLANK([WithDrawalDate]),TODAY(),[WithDrawalDate]) >= CALCULATE( MIN(DateTable[Date]))
))

My date table

DateTable = CALENDAR("01/01/2019","31/12/2020")