Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
quinnjohnson
Helper I
Helper I

Can you make a date visual based off page filters?

I have two date filters I want my report to be filtered by. Essentially i want to show all the premium for policies with an effective date ON or BEFORE the selected date. I also want the same premium filtered by policies with an expiration date AFTER that same selected date. For example, i want to see all the premium for every policy that was effective on or before 10/31/2021 however, some policies will have expired before then as well and i dont want to see those because they are no longer in force. So i want to only see the policies that have an expiration date after 10/31/2021. I have a PolicyEffectiveDate column and PolicyExpirationDate column. 

 

I would like to be able to have the user select a date from a visual that automatically follows these filters. Is this possible? I was thinking some kind of DAX measure maybe but i am not sure. Something along the lines of : 

IF Policy[PolicyEffectiveDate] <= Calendar[Date] AND Policy[ExpirationDate] > Calendar[Date] Return [Current Year Premium]

 

I also have no idea if there is even some sort of single date selector visual.

 

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION
quinnjohnson
Helper I
Helper I

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Contract'[Employee] ),
    FILTER (
        'Contract',
        'Contract'[Start Date] <= MAX ( 'Calendar'[Date] )
            && 'Contract'[End Date] >= MIN ( 'Calendar'[Date] )
    )
)

i found this measure in a different post and it worked perfectly!

View solution in original post

3 REPLIES 3
quinnjohnson
Helper I
Helper I

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Contract'[Employee] ),
    FILTER (
        'Contract',
        'Contract'[Start Date] <= MAX ( 'Calendar'[Date] )
            && 'Contract'[End Date] >= MIN ( 'Calendar'[Date] )
    )
)

i found this measure in a different post and it worked perfectly!

PaulDBrown
Community Champion
Community Champion

Please provide sample data or PBIX file and a depiction of the expected output





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I cannot do this for security purposes. However i can try to make some stuff up that helps:

 

I currently have a measure that sums all the premium within a date range so if the user says "How much premium did we write in October?" they select the month of october on my date slicer and it shows all premium summed with an effective date between 10/1/21 - 10/31/21.

 

However, some of those policies could have been effective on 10/5/21 and then expired on 10/20/21 and it would still show that premium. 

 

My boss wants to see ONLY in force policies that have not expired based off a singular date. He wants to select 10/31/2021 on a date selector and he wants my card showing the premium to ONLY show all active policies with an effective date ON or BEFORE 10/31/2021 AND an expiration date AFTER 10/31/2021. For example lets say Company XYZ bought two policies on 10/1/2021. Policy A was $50,000 and expires on 10/1/2022. Policy B was $100,000 and expired on on 10/5/2021 because the company realized they no longer needed it. My initial card will show $150,000 because both policies were effective between 10/1/2021 - 10/31/2021. However now I need a measure that will just show $50,000 because only Policy A should display since its expiration date is after 10/31/2021. Policy B would not show because it fails to meet the parameter "expiration date AFTER 10/31/2021"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.