Forum Discussion
Converting SUMIF latest v previous dataset in same table
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)
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:
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.
1 Reply
- v-easonf-msft
Community Support
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:
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.