Create Dynamic Backlog Dates Backwards based on week

[eliasayyy]

Hello Everyone I have a complex task to do.

here is Sample Folder Containing Excel and PBIX 

I need to create a line chart weekly to dsiplay backlog

backlog table doesn have a date column hence we need to craft one dynamically based on 2 other measures

Total Backlog = 
CALCULATE(
    DISTINCTCOUNT(
        backlog[PatientId]
    ),
    ALL(dimDate)
)

I need to dynamically Show as of todays week so we are week 52 2025 bt n my sample i am using 2024 so week 52 2024 is max week

this week should show the total backlog

so on max week, backlog should be 463 or whatever the value is based on filter selected excpet date of course

Line chart interacton with dimdate is off so whatever the slicer date is wont affect line chart

to populate the rest of the weeks, i need to use a formula

Backlog of Previous Week = Total Backlog of this week + ( Total Rferrals of Previous Week - Total Scanned of Previous Week)

so in our example, our max date is Week 52 2024

Total Backlog = 463 which will be displayed on week 52 chart

for week 51 formula will be

Backlog for Week 51 = Backlog of week 52  + ( Total Referrals of week 51 - Total Scanned of week 51)

based on picture below

total referrals for week 51 = 150 and total scanned for week 51 is 131

hence week 51 backlog = 463 + ( 150 - 131) = 482

week 50 backlog = 482 + ( 159 - 135) = 506

i created a static Table just to showcase results however this calculated tables doenst respect the dim filters (clinic, contract modality..)

use this table just to reference what im trying to achieve

i need the same logic as this table above but for the backlog, referrals and scanned to be dynamic based on selected filter

So if i select in the filter crawley site and mri modality, 

the backlog now will be not 463 but starts at 36 

for week 51, referrals = 6 , scanned = 12
hence backlog for week 51 = 36 + (6 - 12) = 30

Thank you in advanced

[v-pgoloju]

Hi @eliasayyy,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @eliasayyy,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @lbendlin , @SavioFerraz and @danextian  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[danextian]

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.

[SavioFerraz]

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.

[lbendlin]

backlog table doesn have a date column hence we need to craft one dynamically based on 2 other measures

You cannot create a calculated table based on measures. Well, technically you can, but it will be meaningless as there is no filter context.

Forget about the Backlog table, and focus on creating a backlog measure instead.