cancel
Showing results 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

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Awesome !! Thanks @MFelix

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.