Forum Discussion
Helper IVActive as of
Hi all...
I have a requirement where i have to have 4 date selections on one page
1. Active today (Shows all metrics which are active today)
2. Active as of(Shows all metrics which are active as of selected date)
3. effective Range (shows all metrics dependent on effective date range selection)
4. Expiration Range (shows all metrics dependent on expiration date rage selection)
I have efective range & expiration range as two date columns in my fact table.
I understood how to bring active today (
= IF(TODAY()>=V_DimPolicy[EffectiveDate]&& TODAY()<=V_DimPolicy[ExpirationDate],1,0)
But how do i do active as of....
the idea is to bring client count for all the 4 conditions based on the date selection.
how do I do that arrangement also the measures
Appreciate any help here
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. ā
7 Replies
- anilelmastasi
Super User
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. ā
jostnachsGreat!
But in this case i would have to use three measures/ three views on one page right!
like one view or each bookmark
Hi jostnachs ,
Thank you anilelmastasi for the helpful response!
Yes,If we go with separate slicers and measures for each logic, you would typically need separate visuals or use bookmarks to toggle between views.
Hope this resolve your query.If so, considr accepting it as solution.
Regards,
Pallavi.
Hi jostnachs ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.Hi jostnachs ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank you.Hi jostnachs ,
As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance. If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.
Thank you for your cooperation. Have a great day.
