Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Enrollment For Each Date - DAX Measure

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:

 

memberidentrydatewithdrawaldate
108/01/202009/07/2020
209/19/202010/01/2020
308/17/2020NULL
408/05/202010/15/2020
510/01/2020NULL
609/07/202010/22/2020
708/30/202010/07/2020
809/01/202010/25/2020
909/15/2020NULL

 

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(
 
)
)

 

 

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    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

     

     

     

     

4 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    5 years ago
    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")

     

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    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