Stores Open Between Dates

[bcampbell2]

I need see the number of stores open on a given day. I have an opendate column, a closeddate column (the closeddate will be blank if the store is still open) and a store number. I also have a dim_date table with all dates. What is the best way to see on any given day how many stores are open? I'm assuming a DATESBETWEEN of somekind, but I can't figure it out.

-Thanks

Buddy

[az38 · Accepted solution]

@bcampbell2 

create a measure like

Measure = 
VAR CurrentDate = MAX(CalendarTable[Date])
RETURN
CALCULATE(COUNTROWS('Stores Table'), FILTER('Stores Table', 'Stores Table'[Open Date] <= CurrentDate && ('Stores Table'[Close Date] >= CurrentDate || ISBLANK('Stores Table'[Close Date]) ) )