The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hey Community,
I have been langishing over this for some time.
I have a list of policies with start and end dates and I want to visualize them over time with a total of "active policies" for any given period.
PolicyNo | StartDate | EndDate |
1 | 1/1/2019 | 1/15/2020 |
2 | 1/1/2020 | 2/20/2022 |
3 | 1/2/2020 | 2/20/2022 |
4 | 3/1/2021 |
Some policies don't have end dates because they are continuously billing.
Thank you,
Solved! Go to Solution.
This is magical! I didnt think to look at HR type visualizations. Thank you. Im going to impliment this today and if all is well will mark as the solution.
Hi, @escaddie
You can try the following methods.
Date Table:
Date = CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))
Measure:
active policies =
CALCULATE (
COUNT ( 'Table'[PolicyNo] ),
FILTER (
ALL ( 'Table' ),
[StartDate] <= MAX( 'Date'[Date] )
&& [EndDate] >= MAX('Date'[Date] )
)
)
+ CALCULATE (
COUNT ( 'Table'[PolicyNo] ),
FILTER (
ALL ( 'Table' ),
[StartDate] <= Min( 'Date'[Date] )
&& [EndDate] = BLANK ()
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @escaddie
You can try the following methods.
Date Table:
Date = CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))
Measure:
active policies =
CALCULATE (
COUNT ( 'Table'[PolicyNo] ),
FILTER (
ALL ( 'Table' ),
[StartDate] <= MAX( 'Date'[Date] )
&& [EndDate] >= MAX('Date'[Date] )
)
)
+ CALCULATE (
COUNT ( 'Table'[PolicyNo] ),
FILTER (
ALL ( 'Table' ),
[StartDate] <= Min( 'Date'[Date] )
&& [EndDate] = BLANK ()
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This was great pretty much exactly what I needed. I changed the ALL filters to ALL selected so I could filter them on the visual but other than that works like a charm. Thank you.
This is magical! I didnt think to look at HR type visualizations. Thank you. Im going to impliment this today and if all is well will mark as the solution.
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |