Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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]))
Hi Can some one help. I badly need this fixed. Please find link to my post.
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]))
Hi @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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |