Week on Week Stock Comparrison problems.

SACooper — Wed, 22 Nov 2023 09:45:17 GMT

I am looking to produce a week-on-week comparison of stock levels. We ideally take stock on the same day each week, but occasionally this gets pushed back a day or two and we just accept the variation this will cause.

We capture the date of stock date, type of stock and stock item as well as quatity, with the following fields.

Date created || TypeID || StockItem || Stock
14/11/2023 00:00:00 || 3 || Paper || 89321

14/11/2023 00:00:00 || 4 || Glass || 89321

In my test data we have c.2000 entries covering stock takes on two dates 14/11/2023 and 20/11/2023.

I am using the following DAX

test = VAR thisweek = CALCULATE( MAX('Date'[Start of Week]), 'Date'[Start of Week] = SELECTEDVALUE('Date'[Start of Week]) ) VAR thisweekstock = CALCULATE( SUM('Stock'[instock]), ALL('Stock'), 'Stock'[Date created] >= thisweek, 'Stock'[Date created] < thisweek + 7 ) VAR lastweek = CALCULATE(MAX('Date'[Start of Week]), 'Date'[Date] = thisweek - 7) VAR lastweekstock = CALCULATE( SUM('Stock'[instock]), 'Stock'[Date created] >= lastweek, 'Stock'[Date created] < thisweek ) VAR Change = thisweekstock - lastweekstock VAR Result = SWITCH( TRUE(), Change > 0, "↑", // Up arrow if positive change Change < 0, "↓", // Down arrow if negative change "→" // Sideways arrow if no change ) RETURN Result

when troubleshooting

thisweek produces the correct date "20/11/2023 00:00:00"

thisweekstock produces the correct total and breakdowns per Type ID when placed on a matrix
lastweek produces the correct date "13/11/2023 00:00:00"

However, lastweekstock produces the correct overall total stock for that period, but then replicates that total figure for each row in the matrix rather than showing the total for that TypeID.

I'm a little stumped to say the least.

*SIDENOTE: I will be using better formating on the variable names once this works.

EDIT:

I've also tried the code below with simlar results..

//Orignal code to this point VAR lastweekstock = CALCULATE( SUM('Stock'[instock]), FILTER(ALL('Stock'), 'Stock'[Date created] >= lastweek && 'Stock'[Date created] < lastweek + 7) ) //Original code from this point

Re: Week on Week Stock Comparrison problems.

SACooper — Wed, 22 Nov 2023 12:58:12 GMT

Eureaka!

solved it!

//Orignal code to this point VAR lastweekstock = CALCULATE( SUM('Stock'[instock]), 'Stock'[Date created] >= lastweek. 'Stock'[Date created] < lastweek + 7, ALLEXCEPT('Stock', 'Stock'[StockItem]) ) //Original code from this point

topic Re: Week on Week Stock Comparrison problems. in DAX Commands and Tips

Week on Week Stock Comparrison problems.

Re: Week on Week Stock Comparrison problems.