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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors