Reply
Member
Posts: 90
Registered: ‎04-30-2017
Accepted Solution

Filter Value based on the max date available

[ Edited ]

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

 

DateDollars
  
10/1/2017$1,179
11/1/2017$1,297
12/1/2017$1,427
1/1/2018$1,569

 

Thanks


Accepted Solutions
Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: Filter Value based on the max date available

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]))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post


All Replies
Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: Filter Value based on the max date available

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]))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Member
Posts: 90
Registered: ‎04-30-2017

Re: Filter Value based on the max date available

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

Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: Filter Value based on the max date available

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor
Posts: 7
Registered: ‎10-09-2018

Re: Filter Value based on the max date available

Hi Can some one help. I badly need this fixed. Please find link to my post.

 

https://community.powerbi.com/t5/Desktop/How-to-calculate-Minimum-Qty-between-2-dates-from-a-running...

Highlighted
Frequent Visitor
Posts: 7
Registered: ‎10-09-2018

Re: Filter Value based on the max date available