I am working on a Power BI dashboard where I need to create a bar chart that shows 8 bars for the last 8 weeks. The first bar from the right, shows the last 7 days and every next bar the 7 days before that. This should be dynamically changing based on the selected date.

So when I select a date in the past, the last 7 days (will still be called last 7 days) and will be the 7 days before the selected date, starting on selected date -1. This means the buckets should have dynamically build up weeks. Meaning the 7 days does not correspond with week numbers (the dates in buckets keep shifting)

In my chart I want to visualize the amount of members that joined per 7 day bucket. This is based on my measure that calculates the distinct contractkeys where startdate = dates in buckets.

I have attempted the following but since I work with week index it’s always calculated from the actual current date instead of the selected date:

Y axis:

# Agg CY Joiners = 
VAR CurrentDate = MIN('date'[Date])
VAR StartDate = CurrentDate - 6 -- Adjust to get the start of the 7-day period
RETURN
    CALCULATE(
       [# Base Joiners], 
        Contracts_v1[StartDate] >= StartDate &&
        Contracts_v1[Startdate] <= CurrentDate 
    )

(# Base Joiners is a measure to determine the Joiners that are in scope based on true/false parameters)

X-axis (column in date table)

    "Rolling Week Index",  CEILING(DATEDIFF(today(),[Date], DAY)/ 7,1)

Anyone has experience with this or knows a solution to achieve what I need?

Thank you in advance!