Forum Discussion
Count with filtering different date columns before and after current year
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 , 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)
)
4 Replies
- bhanu_gautam
Super User
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)
)bhanu_gautam Thank you so much! Worked great
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)] ) )
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