Create Dynamic Backlog Dates Backwards based on week

Hi eliasayyy,

Yes, this is achievable using measures (not calculated tables) and a recursive-style calculation with variables.
Key idea: anchor the backlog on the max visible week, then walk backwards using ALLSELECTED(dimDate) so slicers (clinic, modality, etc.) still apply.

Use:

MAXX(ALLSELECTED(dimDate), dimDate[Week]) to detect the latest week

A measure that recalculates backlog per week using
Backlog = AnchorBacklog + SUMX(previous weeks, Referrals − Scanned)

This keeps the line chart dynamic and fully filter-aware.

Helpful sources:

ALL vs ALLSELECTED in DAX: https://learn.microsoft.com/dax/allselected-function-dax

Time intelligence patterns (weekly): https://learn.microsoft.com/dax/time-intelligence-dax

Working with filter context: https://learn.microsoft.com/dax/dax-overview#filter-context

Microsoft Learn (recommended):

Create advanced DAX measures: https://learn.microsoft.com/training/modules/create-measures-dax-power-bi/

Savio Ferraz | Microsoft Learning Consulting | Google Certified Trainer and Microsoft Certified Educator

Did my answer help? Mark my post as a solution or like it if you found it useful.

Hi eliasayyy 

Try the following measures:

Total Referrals PW = 
CALCULATE (
    [Total Referrals],
    FILTER (
        ALL ( dimDate ),
        dimDate[StartOfWeek] = MAX ( dimDate[StartOfWeek] ) - 7
    )
)

==================

Total Scanned PW = 
CALCULATE (
    [Total Scanned],
    FILTER (
        ALL ( dimDate ),
        dimDate[StartOfWeek] = MAX ( dimDate[StartOfWeek] ) - 7
    )
)

=================
Weekly Backlog = 
VAR AnchorWeek =
    CALCULATE (
        MAX ( dimDate[StartOfWeek] ),
        ALL ( dimDate )
    )

VAR ThisWeek =
    MAX ( dimDate[StartOfWeek] )

VAR AnchorBacklog =
    [Total Backlog]

VAR DeltaAfterThisWeek =
    CALCULATE (
        SUMX (
            VALUES ( dimDate[StartOfWeek] ),
            [Total Referrals PW] - [Total Scanned PW]
        ),
        FILTER (
            ALL ( dimDate ),
            dimDate[StartOfWeek] > ThisWeek
                && dimDate[StartOfWeek] <= AnchorWeek
        )
    )

RETURN
IF (
    ThisWeek = AnchorWeek,
    AnchorBacklog,
    AnchorBacklog + DeltaAfterThisWeek
)

The logic for referrals and scanned should be pretty much the same.