Forum Discussion
Using a calculated date to filter all visuals and measures on a page.
I have 2 measures which each provide a specific, singular date, used together to provide a period start and end date.
What I want to do is be able to filter all of my data on my fact table (which has date column) by the start and end date. So really what I need is a way to say filter 'table1'[Date] where date is >[Calculated Start Date] and < [Calculated End Date]
This image shows the X axis min and max range using a formula for a line chart, which works great for filtering my data to only the subset on a specific chart, but I need to actually filter the underlying data so that my measures will ignore data outside of my defined period.
I do not see any way to add a page level filter my date column based on being higher or lower than a measure.
Ideal scenario would be that I have a date slicer using 'between' slicer setting, where the min and max dates were automatically set to my calculated period min and max, that way the user can toggle the date range, but only within the confines of period start and end.
JayhawkDylan , Create a measure like
Sumx(Table, Calculate(Sum(Table[Value]), filter(Table, Table[Date] >= [Start Date] && Table[Date] <= [End Date]) ) )
3 Replies
- amitchandak
Super User
JayhawkDylan , Create a measure like
Sumx(Table, Calculate(Sum(Table[Value]), filter(Table, Table[Date] >= [Start Date] && Table[Date] <= [End Date]) ) )
I think this gets very close to what I need if I remove the sumx, close enough I can figure out how to adjust the specifics measures I am working with from here. Thanks!
Calculate(Sum(Table[Value]), filter(Table, Table[Date] >= [Start Date] && Table[Date] <= [End Date]) )
Hi JayhawkDylan
Measure can be used as a filter on visual, but unfortunately cannot be a page-level or report-level filter.
As you hope to use them to filter the underlying table and want users to filter dates within this period, it would be better to filter the table with Power Query instead. Thus only data within this period will be loaded into Power BI. Filter by values in a column feature description - Power Query | Microsoft Learn
May I know what's the DAX formula of your start date and end date measures? Or how do you decide the start date and end date range? With above GUI, you may only filter by some specific dates, but we can replace them with some dynamic dates by using some Power Query functions.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
