Create moving range of dates with DAX

[dsalony]

I have a dates table.  I want to set up a slicer on the dates, allowing a user to define an end-date.  From that I want to create a set period of indexed dates tied to that variable date that can be used as a filter on other measures.

As an example, if the date range is 1/1/22 to 1/31/22, and a user selects 1/31/22 with the slicer, and the rolling period is set to 7 days, I want the dates from 1/31/22 back to 1/24/22 to be indexed as a '1' and all the other dates in the range to be a '0'; if they select 1/20/22, the index would be from 1/20/22 back to 1/13/22, and all other dates would be '0'.  Thus if I have DAX measures that are tied to dates, and I apply this moving indexed range as a filter, what would be displayed would only be the measures associated with the dates in that range.  Any suggestions?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Index measure: =
IF (
    MAX ( Data[Date] ) <= MAX ( Slicer[Date] )
        && MAX ( Data[Date] )
            >= MAX ( Slicer[Date] ) - 7,
    1,
    0
)

[dsalony]

yep, that solution worked... thank you so much! 😉