Previous N Weeks by Selected Week in Slicer

[Anonymous]

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])))

[v-jayw-msft · Accepted solution]

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

[Anonymous]

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 🙂

[amitchandak]

@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]

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.