Create moving range of dates with DAX

dsalony — Thu, 10 Mar 2022 22:07:46 GMT

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?

Re: Create moving range of dates with DAX

Jihwan_Kim — Fri, 11 Mar 2022 04:08:57 GMT

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 )

Re: Create moving range of dates with DAX

dsalony — Fri, 11 Mar 2022 16:49:59 GMT

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

topic Re: Create moving range of dates with DAX in DAX Commands and Tips

Create moving range of dates with DAX

Re: Create moving range of dates with DAX

Re: Create moving range of dates with DAX