Identifying State history Count based on date slicer

Hello,

Hi ManuBK 

From your examples, it looks like you do not want to count only the rows where the status changed inside the selected dates. You want to count IDs whose state was active at any point during the selected date range.

So the logic should be based on overlap between the slicer period and the status period. In other words, a state should be counted when its start date is on or before the end of the selected range, and its end date is on or after the start of the selected range.

A measure like this should be closer to what you need:

State Count In Selected Period =
VAR MinSelDate = MIN ( 'Calendar'[Date] )
VAR MaxSelDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( History[ID] ),
FILTER (
ALL ( History ),
History[Start date] <= MaxSelDate &&
History[New State Date] >= MinSelDate
)
)

If your visual already uses New State on rows, this measure should return the count for each state automatically.

One thing to verify is whether New State Date represents the last day of the current state or the first day of the next state, because that affects whether the end boundary should be inclusive or exclusive.

Also, if your Calendar table is actively related to only one date column in the history table, that relationship can sometimes interfere with this kind of logic. In that case, using a disconnected date table for the slicer is often a cleaner approach.

If possible, please also share a small sample PBIX or Excel file together with the exact expected result for a few selected date ranges. A public cloud link such as OneDrive, Google Drive, or Dropbox would make it much easier for others to reproduce the scenario and give you a precise solution.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

ManuBK 

Old State Count = 
VAR SlicerMin = MIN('Date'[Date])
VAR SlicerMax = MAX('Date'[Date])
RETURN
COUNTROWS(
FILTER(
ADDCOLUMNS(
VALUES('Table'[ID]),
"@State", 
CALCULATE(
MAX('Table'[Old State]),
'Table'[Start date] <= SlicerMax,
'Table'[New State Date] >= SlicerMin
)
),
[@State] = SELECTEDVALUE('State'[State])
)
)

Counts IDs where state record overlaps slicer range

HI ManuBK,

Have you had a chance to review the solution we shared by DanieleUgoCopp,Ritaf1983,Kedar_Pande? If the issue persists, feel free to reply so we can help further.

Thank you