Forum Discussion

Tanyag's avatar
Tanyag
Frequent Visitor
5 years ago
Solved

Previous Weeks calculation based on another field

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

 

General Comment
Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 years ago

    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

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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