cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Newbie_2020
Frequent Visitor

How to get a value from another table based on the max date selected in a filter?

Hi everyone,

I'm really new to Power BI and need some help.  🙂

Would you be able to help me write a DAX formula to get the YTD Plan based on the max date selected in a date filter?

Ex: I have a date filter and if I select 1/1/2020, 2/1/2020 and 3/1/2020,  I want my measure to pick up 8 , because that's the YTD plan in March.   

 

Table 1

Date
1/1/2020
2/1/2020
3/1/2020
4/1/2020
5/1/2020
6/1/2020
7/1/2020
8/1/2020
9/1/2020
10/1/2020
11/1/2020
12/1/2020

 

 

Table 2

AreaLocationFY PlanDateYTD Plan
A01North301/1/20203
A01North302/1/20205
A01North303/1/20208
A01North304/1/202010
A01North305/1/202013
A01North306/1/202015
A01North307/1/202018
A01North308/1/202020
A01North309/1/202023
A01North3010/1/202025
A01North3011/1/202028
A01North3012/1/202030

 

I attempted to write one, but I didn't get the result I want.

Month YTD Goal = CALCULATE(SUM('Table 2'[YTD Plan]),FILTER('Date','Date'[Date]=MAX('Date'[Date])))
      Result: Month YTD Goal = 191  (not the value I want, this is the sum of YTD plan from 1/1/2020 to 12/1/2020)
 
I would really really appreciate any help.  Thanks so much!
 
-Newbie_2020

 

1 ACCEPTED SOLUTION

Sure, just change the aggregation:

 

Month YTD Goal (Measure) = 
  VAR __MaxDate = MAX('Date'[Date])
RETURN
  SUMX(FILTER(ALL('Table 2'),'Table 2'[Date] = __MaxDate),'Table 2'[YTD Plan])

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Perhaps try:

 

Month YTD Goal (Measure) = 
  VAR __MaxDate = MAX('Date'[Date])
RETURN
  MAXX(FILTER(ALL('Table 2'),'Table 2'[Date] = __MaxDate),'Table 2'[YTD Plan])

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hello @Greg_Deckler,

Thanks so much for your response.  I really appreciate your time. 

The DAX formula you suggested will work if there's only one Area/Location.

What if I have multiple Areas and I want to get the YTD plan based on the max date selected in the filter?  Would you be able to help suggest another DAX formula?

 

Example: If I select dates 1/1/2020, 2/1/2020 and 3/1/2020 in my filter,  my desired output is

 

Area   YTD Plan

A01     8

A02     13

A03     9

 

AreaLocationFY PlanDateYTD Plan
A01North301/1/20203
A01North302/1/20205
A01North303/1/20208
A02South381/1/20206
A02South382/1/202010
A02South383/1/202013
A03West351/1/20203
A03West352/1/20206
A03West353/1/20209

 

Best,

newbie_2020

Sure, just change the aggregation:

 

Month YTD Goal (Measure) = 
  VAR __MaxDate = MAX('Date'[Date])
RETURN
  SUMX(FILTER(ALL('Table 2'),'Table 2'[Date] = __MaxDate),'Table 2'[YTD Plan])

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler  That worked! Yay! Thank you very much! 😃

I just need to remove the ALL after the FILTER.  This is what I used:

 

Month YTD Goal (Measure) = 
  VAR __MaxDate = MAX('Date'[Date])
RETURN
  SUMX(FILTER(('Table 2'),'Table 2'[Date] = __MaxDate),'Table 2'[YTD Plan])

 

Awesome! Glad we got there! 🙂


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors