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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
magnifybi
Resolver I
Resolver I

How to make formula ignore date slicers

If I change the date slicers to full year 2020, or first 6 months it works, but if I change it to July for instance, the measure goes blank, any idea why? Is it because I'm filtering out the data for it to calculate?

 

Quantity Last Week =
Var thisweek = WEEKNUM(today())
Var amount = calculate(Transactions[Quantity Total],vw_DateDim[WeekOfYear]=thisweek-1)
Return amount
 
Any way to ignore the date slicers?
1 ACCEPTED SOLUTION
MattAllington
Community Champion
Community Champion

Your formula only changes the filter on the single [weekofyear] column while retaining any filters on every other calendar column. If you want to modify the entire cal table, you need to change the formula to first remove the filter on the entire table. 

 

Quantity Last Week =
Var thisweek = WEEKNUM(today())
Var amount = calculate(Transactions[Quantity Total],filter(all(vw_DateDim),vw_DateDim[WeekOfYear]=thisweek-1))
Return amount


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

2 REPLIES 2
MattAllington
Community Champion
Community Champion

Your formula only changes the filter on the single [weekofyear] column while retaining any filters on every other calendar column. If you want to modify the entire cal table, you need to change the formula to first remove the filter on the entire table. 

 

Quantity Last Week =
Var thisweek = WEEKNUM(today())
Var amount = calculate(Transactions[Quantity Total],filter(all(vw_DateDim),vw_DateDim[WeekOfYear]=thisweek-1))
Return amount


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Thanks Matt, very helpful

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.