Count with filtering different date columns before and after current year

[Anonymous]

I'm having an issue with a measure where I want to calculate active stores at the start of a selected year 

Which I then want to visualise in a matrix with that selected year and all months.

I've tried the following measure but I'm having an issue where it still removes stores that have a closing date after the selected year. 

And I can't get it right. I'm assuming it has to do with the removefilters part? Which I need (I think?) to be able to filter on a specific year to show in the matrix

Rules, Count as 1 if:

Opening date is less than selected year

Closing date is blank or greater than selected year

Meaning, if we selected 2024, 

Store A has opening date 1985 and closing date 2011, we count as 0

Store B has opening date 1990 and closing date 2025, we count as 1 (not yet closed) (this is where I have an issue)

Store C has opening date 2025 and no closing date, we count as 0 (not yet opened for selected year)

Store D has opening date 2001 and no closing date, we count as 1

CALCULATE(
    COUNTROWS(
        FILTER(
            'StoreTable',
            'StoreTable'[OpeningDate] < MAX(DateTable[Date]) &&  
            (
                'StoreTable'[ClosingDate] > MAX(DateTable[Date]) || 
                ISBLANK('StoreTable'[ClosingDate])
            )
        )
    ),
    REMOVEFILTERS(DateTable),
    USERELATIONSHIP('StoreTable'[OpeningDate], DateTable[Date]),
    USERELATIONSHIP('StoreTable'[ClosingDate], DateTable[Date])
)

For context I have filter on DateTable[Year], single select 

[Anonymous]

@bhanu_gautam 

Follow up questions:

In combination with this measure, in a matrix with 1 year and the 12 months, I'm showing the active, openings and closings (which I get from other tables)

I then need the active and total to carry over the result of adding/removing closings, so it looks something like this:

	January	February	March	April	May	June
Active	199	198	200	200	199	199
Opened		2		1		1
Closed	-1			-2
Total	198	200	200	199	199	200

I've managed to get that calculation working almost perfectly, with the following:

Running active:
VAR _SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR _CurrentMonth = MAX('DateTable'[Month])

VAR _PreviousMonth = IF(_CurrentMonth = 1, 12, _CurrentMonth - 1)
VAR _PreviousYear = IF(_CurrentMonth = 1, _SelectedYear - 1, _SelectedYear)

VAR PreviousActive =
    CALCULATE(
        [Active],
        FILTER(
            ALL('DateTable'), 
            'DateTable'[Month] = _PreviousMonth &&
            'DateTable'[Year] = _PreviousYear
        )
    )

RETURN
PreviousActive + 
CALCULATE(
    [Openings] + [Closings (negative)],
    FILTER(
        ALL('DateTable'), 
        'DateTable'[Month] < _CurrentMonth &&
        'DateTable'[Year] = _SelectedYear
    )
)

Running total:
VAR _SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR _CurrentMonth = MAX('DateTable'[Month])

VAR _PreviousMonth = IF(_CurrentMonth = 1, 12, _CurrentMonth - 1)
VAR _PreviousYear = IF(_CurrentMonth = 1, _SelectedYear - 1, _SelectedYear)

RETURN
CALCULATE(
    [Active] + 
	//[Running Active] +
    SUMX(
        FILTER(
            ALL('DateTable'), 
            'DateTable'[Month] <= _CurrentMonth &&
            'DateTable'[Year] = _SelectedYear
        ),
        [Openings] + [Closings (negative)]
    )
)

But now I have an issue with the year break, 

for example if I have the total as 197 on year-end 2024, when I select 2025 the active for january 2025 it hasn't retained 197 and is instead something else.

Any suggestions on how to keep that? 

The running total is somehow correct, but not the running actual 

[bhanu_gautam · Accepted solution]

@Anonymous , Try using

DAX
ActiveStoresAtStartOfYear =
VAR SelectedYear = SELECTEDVALUE(DateTable[Year])
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'StoreTable',
'StoreTable'[OpeningDate] < DATE(SelectedYear, 1, 1) &&
(
ISBLANK('StoreTable'[ClosingDate]) ||
'StoreTable'[ClosingDate] >= DATE(SelectedYear, 1, 1)
)
)
),
REMOVEFILTERS(DateTable)
)

[Anonymous]

@bhanu_gautam Thank you so much! Worked great

[Anonymous]

@bhanu_gautam 

Follow-up question, 

In conjunction with this measure, I then have two other tables for openings and closings which are more updated than the one used above.

Which I then use to visualise active + opening - closings, 

which I then want to carry over onto the next month, for both the total and the active. 

Like this: 

	January	February	March	April	May	June
Active	199	198	200	200	199	199
Opened		2		1		1
Closed	-1			-2
Total	198	200	200	199	199	200

And the measure I have worked great for this, until I changed it just now. 

Now the active doesn't seem to properly get the previous end of year count

For example, let's say the table above is 2024 and december insteaf of june, then if I filter 2025 I want january to be 200

I've used following measures for active (based on the one you just helped me with, and another one for total.

Running Active = 
VAR _SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR _CurrentMonth = MAX('DateTable'[Month])

VAR _PreviousMonth = IF(_CurrentMonth = 1, 12, _CurrentMonth - 1)
VAR _PreviousYear = IF(_CurrentMonth = 1, _SelectedYear - 1, _SelectedYear)

VAR PreviousActive =
    CALCULATE(
        [Active],
        FILTER(
            ALL('DateTable'), 
            'DateTable'[Month] = _PreviousMonth &&
            'DateTable'[Year] = _PreviousYear
        )
    )

RETURN
PreviousActive + 
CALCULATE(
    [Openings] + [Closings (negative)],
    FILTER(
        ALL('DateTable'), 
        'DateTable'[Month] < _CurrentMonth &&
        'DateTable'[Year] = _SelectedYear
    )
)

Running Total = 
VAR _SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR _CurrentMonth = MAX('DateTable'[Month])

VAR _PreviousMonth = IF(_CurrentMonth = 1, 12, _CurrentMonth - 1)
VAR _PreviousYear = IF(_CurrentMonth = 1, _SelectedYear - 1, _SelectedYear)

RETURN
CALCULATE(
    [Active] + 
	//[Running Active] +
    SUMX(
        FILTER(
            ALL('DateTable'), 
            'DateTable'[Month] <= _CurrentMonth &&
            'DateTable'[Year] = _SelectedYear
        ),
        [Openings] + [Closings (negative)]
    )
)