Converting SUMIF latest v previous dataset in same table

[petercowen]

I need some help converting a simple excel sumif in to the correct DAX equation

I have a single table that contains multiple weeks worth of data and I want to be able to compare the week's quantity versus last week's, but I cannot get it to pull the data in.  

Columns A to C are as per the data sets loaded and, if it was Excel, I'd create column D to make them unique.  The equation below is how I'd get the Last Week data to show, but I cannot convert this to a DAX formula that works

=SUMIF(D:D,A2&(B2-1),C:C)

[v-easonf-msft · Accepted solution]

Hi, @petercowen 

According to your description, you can try measure like that:

Last week Qty =
CALCULATE (
    SUM ( 'Table'[Qty] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Material] ),
        'Table'[Week No]
            = MAX ( 'Table'[Week No] ) - 1
    )
)

Here is a sample I made:

url: https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EQ7_I60lHOFFg97kdRK4n...

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.