Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |