# Calculate Waiting List Variation in Power BI

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

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)

@upwardD Let me know if this works for you:

``````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

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