Calculate Waiting List Variation in Power BI

DAX DAX Calculations
1

Hi,
I have a bit of complex logic to apply to get the result that I want but not sure how to do it.

image
image1362×860 96.3 KB

Total Goods columns are goods that arrived at the warehouse on a given week. The completed column is the total of goods delivered and the Gross is the leftover or undelivered goods. The Waiting List column is what I want to calculate. The logic for this column is to add the Gross from the previous week, e.g, 2021-01 which is 928 to the following week’s total ( since the goods could not be delivered) which is 1,475. Whatever leftover goods in the Gross column should be added to next week workload.
I can’t think of how to make this work for now.
I anticipate your help.
PreviousNcurrent.pbix (365.4 KB)

2

@upwardD Let me know if this works for you:

image
image556×582 16.3 KB

image
image744×656 37.8 KB 

Waiting List = 
// not the the right measure. Just for placeholder
VAR TotalGoods = [Total Goods]
VAR FirstWeekWithTransaction =
    CALCULATE (
        MINX ( SUMMARIZE ( Sales, 'Date'[WeeknYear] ), 'Date'[WeeknYear] ),
        ALLSELECTED ( Sales )
    )
VAR CurrentWeekYear =
    SELECTEDVALUE ( 'Date'[WeeknYear] )
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR LastDateSalesTable =
    CALCULATE ( MAX ( Sales[Date] ), ALLSELECTED ( Sales ) )
VAR ProductWeekYearTotal =
    CALCULATE (
        VAR ProductWeekYear =
            SUMMARIZE ( Sales, 'Date'[WeeknYear], Products[Product] )
        VAR ProductWeekYearGross =
            ADDCOLUMNS ( ProductWeekYear, "@Gross", [Gross] )
        VAR ImmediateLastWeek =
            MAXX ( ProductWeekYearGross, 'Date'[WeeknYear] )
        VAR PreviousWeekGross =
            SUMX (
                FILTER ( ProductWeekYearGross, 'Date'[WeeknYear] = ImmediateLastWeek ),
                [@Gross]
            )
        VAR Result = TotalGoods + PreviousWeekGross
        RETURN
            Result,
        'Date'[WeeknYear] < CurrentWeekYear,
        'Date'[WeeknYear] >= FirstWeekWithTransaction,
        ALLEXCEPT ( Sales, Products[Product], 'Date' ),
        ALLSELECTED ( Products[Product] ),
        ALLSELECTED ( 'Date' )
    )
VAR Result =
    IF (
        LastVisibleDate <= LastDateSalesTable 
            && TotalGoods <> 0,
        ProductWeekYearTotal
    )
RETURN
    Result
5 Likes
3

This cool!
I am so delighted. Thank you very much