Creating a Dynamic Slider that Selects Inforce Policies Only that does X with the data.

[qwertyh]

Hello,

I just started my journey with PowerBI and Power Query and would greatly appreciate some assistance. I am trying to create a slider such that depending on the Year and Quarter that I select for Example 2017 Q2, it will filter my data by checking if that date lies between the effective date and expiration date and pull up the appropriate revenue in Card.

Effective_date	Expiration Date	revenue
6/13/2015	6/12/2016	$4,253,000
4/20/2017	4/20/2018	$14,254,000
4/20/2017	4/20/2018	$14,254,000
4/20/2014	4/20/2015	$14,254,000
4/4/2017	4/4/2018
9/5/2017	9/5/2018
4/4/2017	4/4/2018
4/4/2017	4/4/2018
6/10/2018	6/10/2019	$47,750,999
7/1/2017	7/1/2018	$27,037,800

 
How should I set up my measure(s) and tables such that I can do something like this? I realize that this may require a long explanation but I have spent half-a-day already banging my head against this so any help would be greatly appreciated.

Thank you.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[qwertyh]

Thank you for the quick response: let me be clearer with my ask.

In my excel sample, if I selected 2017 Q2, the card should be showing 28.508M from the two 14M in row 2 & 3, since that revenue lies between the effective and expiration date.

From the pbix file, I see that it is dividing the revenue to a revenue per day and only summing up the revenue for the particular Quarter Year selected. It's different than what I had in mind.

Thanks again for trying to help.

[Ashish_Mathur]

Hi,

I just do not understand.  Row 2 spans one entire year.  So then why should the revenue for one quarter be the same as the revenue for the entire year.  I am absolutely confused.

[qwertyh]

This is the way how it is done for inforce policy for insurance. For inforce line premium, they are looking the total premium for all the policies that still active and talking the sum of their premiums without taking into account what part of each policy premiums lies in the quarter/day/weekly/etc view.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.