Hi
I’m trying to work out the number of active members of a monthly giving programme on any given day.
I know how to calculate this in Excel but am struggling to get the formula right in Power BI.
The data looks like:
The Excel formula that gives the correct result based on a given date (in cell J8 on the spreadsheet) is:
=COUNTIFS('Active Members'!B:B,"<="&J8,'Active Members'!D:D,"Yes")+COUNTIFS('Active Members'!B:B,"<="&J8,'Active Members'!C:C,">"&J8)
Basically for any given day (for example 24 March 2020), to find the number of active members on that day:

count all the members with a ‘Start Date’ less than and equal to the given day as long as 'Current Member = “Yes”

count all the members with a ‘Start Date’ less than and equal to the given day as long as the ‘End Date’ is greater than the given day

Add the two together to give the number of active members on the given day
4, Make sure no other filters mess with the calculation
I won’t bore you with all the DAX formulas I’ve tried. However, any guidance would be greatly appreciated.
Thanks.