cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

DAX Filter

HI Guys,

 

I have a quick question:
- Lets say I filtered my entire report on Calendar Date is in "Previous Week"

- But I need one of my measures to check if something was assorted in the last 6 months

 

I used the formula:

Assortment = CALCULATE([Assorted], all('Calendar'), DATESBETWEEN('Calendar'[Calendar Date], today() -180, today()))
 
Is the measure actually calculating on the last 6 months or is the report level filter only limitting it to previous week data ? If the latter, then how do I proceed
1 ACCEPTED SOLUTION
Community Support
Community Support

@powpowpow 

The page filter only changes the displayed visual, the calculating result still take the last 6 months value.

For example, I have a measure calculate the last month value, when I set page filter to before 2020/2/1. The result 193 doesn't change, only the visual is filtered. 

6.JPG

 

Best, regards

Paul Zheng

View solution in original post

4 REPLIES 4
Community Support
Community Support

@powpowpow 

The page filter only changes the displayed visual, the calculating result still take the last 6 months value.

For example, I have a measure calculate the last month value, when I set page filter to before 2020/2/1. The result 193 doesn't change, only the visual is filtered. 

6.JPG

 

Best, regards

Paul Zheng

View solution in original post

Hi Paul, 

 

That's what I thought thanks a lot for confirming ! 😄 

 

The weird thing is that for another measure of the same type, I was trying to sum all shipments that were made in the last three weeks. Knowing that weeknum([Last Saturday]) is 8 and that the visual is filtered on week 8 data only (Variable Fiscal Week is set to Previous Week), I assumed that the below formula would give me the result the sum of shipments made on week 6, 7 and 8. Unfortunately, it only sums the shipments made on week 8, thus limited by the report level filter. 😞 

 

Transit = CALCULATE(SUM(Shipments[Gross Units]),

ALL('Calendar'[Variable Fiscal Week]), 

filter(all('Calendar'),'Calendar'[Calendar Week] > (weeknum([LastSaturday],1) - 3) && 'Calendar'[Fiscal Week Num] <= (WEEKNUM([LastSaturday],1)))

 

Data:  I have

Calendar week = 6   -> Shipments = 6 units

Calendar week = 7   -> Shipments = 3 units

Calendar week = 8   -> Shipments = 12 units

 

But Transit measure results in 12 (shipments on week 8, Weeknum of last saturday)

Instead of summing of the three and be 21 units

 

Any idea on why it is doing this and how to resolve ? would greatly appreciate

 

 
 
 
Super User IV
Super User IV

Seems fine or you can try like example below

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-6,MONTH))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

I would expect that the ALL should get rid of the page level filter if it is filtering the Calendar table.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors