Forum Discussion
Responsive ResidentFilter Value based on the max date available
Hello, I typically try to exhast all the searches but I've been serching for a while and there are plenty of more complex examples but I can't get this figured out.
I have a data model and have built a dashboard with multiple visuilizations but I want to filter on the max date available since data is released monthly. In this very simple example below I have date table called 'DimDate', and I have dollar sales which is in a customer table - lets call it 'Customer'.
I only want to return the data for 1/1/2018 as soon as it is added to the data model. Then the next month I want it to update to 2/1/2018 when that data is added.
Is there a way to write a DAX filter using the MAX date function or do I need to write a new measure for each table I want filtered?
I tried to use the quick measures function to filter sales on the max category. Category being the DimDate and it is showing the sales for the next to last date.
I have also tried
Measure = CALCULATE(SUM('Customer'[Dollars]), FILTER(ALL(DimDate[Date]) , DimDate[Date] = MAX(DimDate[Date])))
In the below exampleI want the result to be $1,569
| Date | Dollars |
| 10/1/2017 | $1,179 |
| 11/1/2017 | $1,297 |
| 12/1/2017 | $1,427 |
| 1/1/2018 | $1,569 |
Thanks
from the description you posted, the date you want is in the customer table and the filter is on the date table. So try this
Measure = CALCULATE(SUM('Customer'[Dollars]), FILTER(ALL(DimDate[Date]) , DimDate[Date] = MAX(Customer[Date]))
5 Replies
- MattAllington
Community Champion
from the description you posted, the date you want is in the customer table and the filter is on the date table. So try this
Measure = CALCULATE(SUM('Customer'[Dollars]), FILTER(ALL(DimDate[Date]) , DimDate[Date] = MAX(Customer[Date]))
jpt1228Hi MattAllington I see why I couldn't get it to work. I didn't think I had to use the date column in the table when I had a date table. Is there a way to write formula to filter the page to the latest date (Or max date).
I have a dashboard that has visuals of sales by region, product, customer, warehouse etc.
I would like to be able to filter all the visuals based on the max date that is in the table. And when new data is added I would like to change all the visuals to reflect the new data.
These are all coming from the same 'Customer' table so the dates would be the same.
Thanks
- MattAllington
You could create a calcualted column in your calendar table with a formula like =if(calendar[date]=MAX(dataTable[Date]),"latest")
This will give you a column that you can filter on in your report. The column only has 2 possible values, plus it is in a lookup table, so this calculated column is fine
Hi Can some one help. I badly need this fixed. Please find link to my post.
