cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brubaker2112
Regular Visitor

Date Filter - As At Date between a start and end date

I have a dataset that contains cover from and cover to dates.  I would like to be able to filter these down, ideally with a slicer, such that I can select a date and then dynamically calculate whether that selected date is between the cover from and cover to dates.

 

I have got as far as creating a separate date table and producing a measure that represents the SelectedDate when a single date value is picked but I am having trouble getting the dynamic calculation to recognise this selected date.

1 ACCEPTED SOLUTION

Accepted Solutions
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Date Filter - As At Date between a start and end date

Hi @Brubaker2112 ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Date Filter - As At Date between a start and end date

Hi @Brubaker2112 ,

I still have a little confused about your scenario.

If it is convenient, could you share some data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brubaker2112
Regular Visitor

Re: Date Filter - As At Date between a start and end date

Hello,

 

A cut down sample of my data would be as below - I am trying to create the column "LiveAsAtDate" to work dynamically with a change in slicer (which has a selection of dates in it).  In Excel this would basically be an IF(DateFrom<AsAtDate,If(DateTo>AsAtDate,"Yes","No"),"No").

 

As At Date16/03/2019  
    
RecordDate FromDate ToLiveAsAtDate
101/01/201931/12/2019Yes
201/01/201928/02/2019No
301/03/201928/02/2020Yes
401/04/201906/09/2019No
515/03/201931/12/2019Yes
614/06/201915/06/2021No
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Date Filter - As At Date between a start and end date

Hi @Brubaker2112 ,

Assuming that the As at date is the selectedvalue from date slicer.

Then you could create the measure below to achieve your desired output.

Measure 2 =
VAR a =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF (
        MAX ( 'Table3'[Date From] ) < a,
        IF ( MAX ( 'Table3'[Date To] ) > a, "Yes", "No" ),
        "NO"
    )

Here is the output.

Untitled.png

In addition, you could refer to my attachment.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Date Filter - As At Date between a start and end date

Hi @Brubaker2112 ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Brubaker2112
Regular Visitor

Re: Date Filter - As At Date between a start and end date

Thanks Cherry,

 

This has done the job.

v-piga-msft Resident Rockstar
Resident Rockstar

Re: Date Filter - As At Date between a start and end date

Hi @Brubaker2112 ,

You're welcome!Smiley Wink

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors