Identifying State history Count based on date slicer

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