Previous Weeks calculation based on another field

[Tanyag]

I have a data  for each week with fields RTYPE, and weekly QTY and I have DAX that calculates QTY for Previous Week but I also would like to add "RTYPE"  creiteria before I calculate Previous Week's QTY. As of Now its ignoring the RTYPE and just calculates QTY for Previous Week .

My DAX = 

Prev Wk =

VAR CurrentWeek =

SELECTEDVALUE ( 'qryAppendAll'[week num])

VAR CurrentYear =

SELECTEDVALUE ( qryAppendAll[year] )

VAR MAXWeekNo =

CALCULATE ( MAX ( qryAppendAll[week num] ), ALL ( qryAppendAll[Week] ) )

RETURN

(

SUMX (

FILTER (

ALL ( qryAppendAll ),

IF (

CurrentWeek = 1,

qryAppendAll[week num] = MAXWeekNo

&& qryAppendAll[year] = CurrentYear - 1,

qryAppendAll[week num] = CurrentWeek - 1

&& qryAppendAll[year] = CurrentYear

)

),

[Qty]

)

)

What is the best strategy to include RTYPE  selection criteria 

TIA

[MFelix · Accepted solution]

Hi  , 

You issue is that you are making the calculation based on the ALL syntax for the table qryAppendAll , in this case you should add the RTYPE to your filtering also something similar to this:

Prev Wk =
VAR CurrentWeek =
    SELECTEDVALUE ( 'qryAppendAll'[week num] )
VAR CurrentYear =
    SELECTEDVALUE ( qryAppendAll[year] )
VAR MAXWeekNo =
    CALCULATE ( MAX ( qryAppendAll[week num] ), ALL ( qryAppendAll[Week] ) )
RETURN
    (
        SUMX (
            FILTER (
                ALL ( qryAppendAll ),
                IF (
                    CurrentWeek = 1,
                    qryAppendAll[week num] = MAXWeekNo
                        && qryAppendAll[year] = CurrentYear - 1,
                    qryAppendAll[week num] = CurrentWeek - 1
                        && qryAppendAll[year] = CurrentYear
                )
                    && qryAppendAll[Rtype] = SELECTEDVALUE ( qryAppendAll[Rtype] )
            ),
            [Qty]
        )
    )

@Tanyag

[Tanyag]

Awesome !! Thanks @MFelix