Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

Capture.PNG

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
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.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
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 🙂

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.