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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpt1228
Responsive Resident
Responsive Resident

Filter 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

 

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

 

Thanks

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



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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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...

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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



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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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