Active as of

Hello jostnachs ,

For Active Today:

Client Count Active Today =
CALCULATE(
DISTINCTCOUNT(V_DimPolicy[ClientID]),
V_DimPolicy[ActiveTodayFlag] = 1
)

For Active As Of:

-Create a disconnected date table (e.g., Date_AsOf)
You can generate this using Power BI's "Enter Data" or use a date table and remove relationships.

-Create a slicer using Date_AsOf[Date].

Create the measure:

Client Count Active As Of =
VAR _selectedDate = SELECTEDVALUE(Date_AsOf[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(V_DimPolicy[ClientID]),
V_DimPolicy[EffectiveDate] <= _selectedDate &&
V_DimPolicy[ExpirationDate] >= _selectedDate
)

For Effective Range Selection:

Use your existing EffectiveDate column with a slicer in between mode.

Client Count Effective Range =
CALCULATE(
DISTINCTCOUNT(V_DimPolicy[ClientID])
// EffectiveDate slicer filters this automatically
)

For Expiration Range Selection:

Same as above, use ExpirationDate in a slicer.

Client Count Expiration Range =
CALCULATE(
DISTINCTCOUNT(V_DimPolicy[ClientID])
// ExpirationDate slicer filters this automatically
)

Make sure:

Your Date_AsOf slicer has no relationship to the fact table.

The other slicers (EffectiveDate, ExpirationDate) do filter the fact table directly.

If this solved your issue, please mark it as the accepted solution. ✅