cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Previous N Weeks by Selected Week in Slicer

Hi All,

I have always use this formula to display data for previous N weeks and it works well when I am caculating from only one table.

```CALCULATE (  SUM(tableName[column])
FILTER (
ALL ( yourTable ),
dim_date[RANKWEEK] <= SELECTEDVALUE( dim_date[RANKWEEK] )
&& dim_date[RANKWEEK]
> SELECTED VALUE( dim_date[RANKWEEK] ) - 6 )
)```

Drag the week end date from the tableName and the measure into my visual . Then drag the week end from dimDate as the slicer and it works.

But now I have an issue I want to do the same thing, however, my measure this time is refering to two different tables to calculate. So now when I only drag in the weekend date from tableA the context of the measure is all wrong... I am guessing its something to do with filter contexty.. but I really dont know how to resolve it.

My desire output is in the top green box in picture below - but I would like it to just filter for the three week ending 17th, 10th, 13th. However, the output is calculating incorrectly.

My dax is like so.

``````AvgSale =
Var _sales = SUM(Sales[Sales])
Var _Qty = SUM(Qty[Qty])
RETURN DIVIDE(_sales,_Qty)
----------------------------------------------------
AvgSaleP3W = CALCULATE([AvgSale], FILTER(All(dimDate), dimDate[Rank] > SELECTEDVALUE(dimDate[Rank])-3 && dimDate[Rank] <= SELECTEDVALUE(dimDate[Rank])))``````

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Inactive the relationship between fact tables and dim table.

Use the date in the fact table as axis.

Make sure the this measure you provided above could return correct value.

``````AvgSale =
Var _sales = SUM(Sales[Sales])
Var _Qty = SUM(Qty[Qty])
RETURN DIVIDE(_sales,_Qty)``````

Create another measure like below:

``````measure =
VAR week1 =
SELECTEDVALUE ( fact[week] )
VAR week2 =
SELECTEDVALUE ( dim[week] )
RETURN
IF ( week1 > week2 - 3 && week1 <= week2, 1, 0 )
``````

At last add this measure to visual filter and set value = 1.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
4 REPLIES 4
Community Support

Hi @Anonymous ,

Inactive the relationship between fact tables and dim table.

Use the date in the fact table as axis.

Make sure the this measure you provided above could return correct value.

``````AvgSale =
Var _sales = SUM(Sales[Sales])
Var _Qty = SUM(Qty[Qty])
RETURN DIVIDE(_sales,_Qty)``````

Create another measure like below:

``````measure =
VAR week1 =
SELECTEDVALUE ( fact[week] )
VAR week2 =
SELECTEDVALUE ( dim[week] )
RETURN
IF ( week1 > week2 - 3 && week1 <= week2, 1, 0 )
``````

At last add this measure to visual filter and set value = 1.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Thanks a lot for this 🙂

I ended up created a table and using the summarize function to bring in both the values for each week and type then use that tables week end date as my axis and it works.

Will keep what you suggested in mind for future 🙂

Super User

@Anonymous , If you are using two tables, Both Should join to date table and the slicer and visual should use columns from the date table for Date, Week, Month , year etc

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Anonymous
Not applicable

Hi, yes I have this set up in my actual power bi report.

The only issue is that when I drag the week end date from my dimDate table - it will filter for the selected week in my slicer. The measure doesnt filter for the last N weeks from the selected week.

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors