Identifying State history Count based on date slicer

[ManuBK]

Hi ,

I have a requirement where in i need to identify the Old state count based on the date slicer.

I need to indentify the state count based on the date selection on that period 

For example 1- If i select a date range from a slicer date as 3-9-2026 to 3-11-2026 then my state count for  Onhold should be 1 as In this date range Onhold is falling under

Example 2 - If i select date range from 3-14-2026 to 3-16-2026 then it should show 1 count under Inprogress 

Could you please help me on this 

@PowerBI @superuser @mh2587 

ID	Old State	New State	Start date	New State Date
1111		New	3/2/2026	3/2/2026
1111	New	Inprogress	3/2/2026	3/4/2026
1111	Inprogress	Inprogress	3/4/2026	3/5/2026
1111	Inprogress	Onhold	3/5/2026	3/10/2026
1111	Onhold	Onhold	3/10/2026	3/12/2026
1111	Onhold	Onhold	3/12/2026	3/12/2026
1111	Onhold	In progress	3/12/2026	3/13/2026
1111	Inprogress	Completed	3/13/2026	3/14/2026
2222		New	3/14/2026	3/14/2026
2222	New	Inprogress	3/14/2026	3/15/2026
2222	Inprogress	Inprogress	3/15/2026	3/15/2026
2222	Inprogress	Onhold	3/16/2026	3/17/2026
2222	Onhold	Inprogress	3/17/2026	3/19/2026
2222	Inprogress	Onhold	3/19/2026	3/21/2026
2222	Onhold	In progress	3/21/2026	3/23/2026
2222	Inprogress	Completed	3/23/2026	3/27/2026

[v-saisrao-msft · Accepted solution]

HI @ManuBK,

I have recreated your requirement in Power BI using sample data and attached a sample PBIX file for your reference. You may also find the following Microsoft documentation helpful, as it aligns with this approach.

Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Thank you.

[v-saisrao-msft · Accepted solution]

HI @ManuBK,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[ManuBK]

Hi @v-saisrao-msft Issue has been resolved. Thank you soo much for your help . Really appreciated

[v-saisrao-msft]

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

[Kedar_Pande]

@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

[ManuBK]

Hi @Kedar_Pande  Thanks for your reply .Apologise for late response. I have three questions on above measure

VAR SlicerMin = MIN('Date'[Date]) As this needs to be as calendar date right ?

[@State] = SELECTEDVALUE('State'[State])  The bold value is the old state or the new state as when i copy the code and placed in PBI it says Cannot find the name State

And for Calendar date which column has been used for a relationship -  Start date or New Start date ?

[v-saisrao-msft · Accepted solution]

HI @ManuBK,

I have recreated your requirement in Power BI using sample data and attached a sample PBIX file for your reference. You may also find the following Microsoft documentation helpful, as it aligns with this approach.

Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Thank you.

[Ritaf1983]

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

[DanieleUgoCopp]

Hello,

I guess that the issue here is you’re treating states as events, while your logic really needs intervals, each state is valid from one date until the next change so instead of counting rows, you should build a “valid from / valid to” range per row, basically Start = New State Date, End = next New State Date (per ID), then your slicer should check if the selected period overlaps that interval in DAX terms, something like checking if Start <= slicer max date and End >= slicer min date, that way you count the state if it was active at any point in the selected range if you don’t do this, Power BI just counts transitions, not the actual state during the period, which is why your results feel off I’d try first creating a calculated column for the “next date” per ID (using LEAD logic or EARLIER pattern), then build the measure on top of that, much easier to control

Best regards,
Daniele