Forum Discussion
Help with setting up a model - beginner
- 10 months ago
Hi - and thank you!
The issue has been solved. Unfortunately I had to clean up my dates a lot (debited until lasting until year 2900 for example).
Here’s a clean, reliable pattern to get “active members on the last date selected”:
- Calendar (disconnected)
Calendar = CALENDAR ( DATE(2018,1,1), DATE(2099,12,31) )
Use Calendar[Date] as your slicer (single- or multi-date is fine — we’ll take the max).
- Relationships
* Person[member_id] (1) → (∗) subscription_only_memberships[member_id]
* No relationship from Calendar (keep it disconnected).
- Measure
Active Members (as of last selected date) :=
VAR d =
MAX ( Calendar[Date] ) -- last date chosen in slicer
RETURN
CALCULATE (
DISTINCTCOUNT ( Person[customer_number] ),
KEEPFILTERS (
FILTER (
subscription_only_memberships,
subscription_only_memberships[start] <= d
&& (
ISBLANK ( subscription_only_memberships[debited until] )
|| subscription_only_memberships[debited until] >= d
)
)
)
)
I hope it helps. If so, please give kudoes and accept it as a solution. Thanks
Hi - and thank you!
The issue has been solved. Unfortunately I had to clean up my dates a lot (debited until lasting until year 2900 for example).