cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ladyhaley
Resolver I
Resolver I

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

 

Capture.PNG

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @ladyhaley ,

 

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.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

Hi @ladyhaley ,

 

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.

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
Super User
Super User

@ladyhaley , 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-bd52912a5b...

 

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors