Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SACooper
Helper II
Helper II

Week on Week Stock Comparrison problems.

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

 

 

1 ACCEPTED SOLUTION
SACooper
Helper II
Helper II

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

 

View solution in original post

1 REPLY 1
SACooper
Helper II
Helper II

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors