Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I have a list of events with an activation and inactivation date. I would like to create a measure which counts the number of active events (i.e. events where this condtion is true: ActiveDate < GivenDate && GivenDate < InactivDate || ISBLANK(InactivDate)) for any give date within the current context of the view.
Example Data for eventframes:
Name | ActiveDate | InactiveDate |
ABC | 2022-01-01 | 2022-02-01 |
DEF | 2022-03-04 | 2022-07-01 |
GHI | 2022-04-06 |
Example Data for dim_date:
Date |
2022-01-01 |
2022-01-02 |
... |
There are 1:n relationships between Date and ActiveDate (active) and between Date and InactiveDate (inactive).
This is my current approach:
Standing_events =
VAR __currentDate = LASTDATE(dim_Date[Date])
RETURN
(
CALCULATE(COUNTROWS(eventframes),
FILTER(
eventframes,
eventframes[ActiveDate] < __currentDate && (__currentDate < eventframes[InactiveDate] || ISBLANK(eventframes[InactiveDate]))
)
))
It works fine if I create a new table with a row for every date but returns an empty result if I use it as a measure.
Edit:
This worked for me:
Standing_events =
CALCULATE(
COUNTX(
FILTER(eventframes,eventframes[ActiveDate]<=max('dim_date'[Date]) && (ISBLANK(eventframes[InactiveDate]) || eventframes[InactiveDate]>max('dim_date'[Date]))),
(raise_recover_eventframes[Eventname])),CROSSFILTER(eventframes[ActiveDate],'dim_date'[Date],None))
@amitchandak : Thanks!
Solved! Go to Solution.
@chris2 , Seem the case is very similar to my HR blog. Refer if blog or attached files can help
Hi,
Please check the below picture and the attached pbix file.
Standing_events =
COUNTROWS (
FILTER (
ALL ( eventframes ),
eventframes[ActiveDate] <= MAX ( dim_Date[Date] )
&& OR (
eventframes[InactiveDate] >= MIN ( dim_Date[Date] ),
eventframes[InactiveDate] = BLANK ()
)
)
) + 0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This returned a count of all events. Anyway thank you for your effort.
@chris2 , Seem the case is very similar to my HR blog. Refer if blog or attached files can help
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |