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

[Brubaker2112]

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.

[v-piga-msft]

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

[Brubaker2112]

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 Date	16/03/2019
Record	Date From	Date To	LiveAsAtDate
1	01/01/2019	31/12/2019	Yes
2	01/01/2019	28/02/2019	No
3	01/03/2019	28/02/2020	Yes
4	01/04/2019	06/09/2019	No
5	15/03/2019	31/12/2019	Yes
6	14/06/2019	15/06/2021	No

[v-piga-msft · Accepted solution]

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

[Brubaker2112]

Thanks Cherry,

This has done the job.

[v-piga-msft]

Hi @Brubaker2112 ,

You're welcome!

Best Regards,

Cherry

[v-piga-msft]

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.

In addition, you could refer to my attachment.

Best Regards,

Cherry