topic Re: Measure to get a running total for policies that billed only during a specific period in DAX Commands and Tips

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

escaddie — Tue, 09 Aug 2022 20:50:01 GMT

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,

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

amitchandak — Wed, 10 Aug 2022 03:29:57 GMT

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-trend/ba-p/882970

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

escaddie — Wed, 10 Aug 2022 15:28:13 GMT

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.

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

v-zhangti — Wed, 17 Aug 2022 06:34:04 GMT

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.

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

escaddie — Wed, 17 Aug 2022 13:47:48 GMT

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.