Measure to get a running total for policies that billed only during a specific period

[escaddie]

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, 

[escaddie · Accepted 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.

[v-zhangti · Accepted 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.

[escaddie]

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.

[amitchandak]

@escaddie , refer to my blog and the attached file after the signature on the same topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[escaddie · Accepted 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.