Access dyanmic date filter in Beast mode

I decided to cross join your data with a calendar table to help me fill in each day an account was active.

You can find the Calendar data set by searching for Domo in the cloud app connectors.  Domo Dimensions has a calendar data set you can use.

I then joined it in a MySQL dataflow with the following transforms:

SELECT
	a.*
    ,b.`dt` as `Date`
FROM
	`dojo_user01546` a 
    CROSS JOIN 
    `calendar` b 
WHERE a.`Date Opened`<=b.`dt` AND ifnull(a.`Date Closed`, DATE_ADD(CURDATE(), INTERVAL 30 DAY)) >= b.`dt`
ORDER BY a.`Account`,b.`dt`

SELECT
	a.*
    ,case when `Date Opened`<=`Date` and ifnull(a.`Date Closed`, DATE_ADD(CURDATE(), INTERVAL 30 DAY))>=`Date` THEN 'Active' else 'Closed' END as `Account Status`
FROM `transform_data_1` a 

I chose to add 30 days to the current date as the "close" date of any account that was still open.  I suppose you could change this to whatever your cancellation policy might state.  If an account can close at any time then you may want to just leave it as curdate().

With this data set, you can create a card and mae a beastmode to calculate the number of active accounts for any given day:

COUNT(DISTINCT 
     case when `Account Status`='Active' then `Account` end)

Can you just use a case statement to count if the close date is either null or higher than the selected range?  The filter would be on the dates already, so you could probably use max date opened as the criteria in the beast mode. 

If the nulls are an issue, as they often are - you can address this in your ETL by setting null values to a date in the future. 

One solution I have seen is setting the close date a week ahead of 'Current Date'.  That way, it's dynamic and you can never catch it, but the field is always populated.  Gets tricky if the field is visible to others, so you can do this in a new field, where all rows have values, but it's only used for criteria/calculation, and not visualizations.  

To @ST_-Superman-_ 's point, it does get trickier when you want to count a location if it was active at all during the filtered period.  Use of date functions like those I just posted in another discussion could be used, perhaps.  

In my suggestion, you would be filtering on the first date column, and doing a count based on the second.  The first would be a regular filter.  The second would be what you would achieve in the beast mode.