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

[Newbie_2020]

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

Area	Location	FY Plan	Date	YTD Plan
A01	North	30	1/1/2020	3
A01	North	30	2/1/2020	5
A01	North	30	3/1/2020	8
A01	North	30	4/1/2020	10
A01	North	30	5/1/2020	13
A01	North	30	6/1/2020	15
A01	North	30	7/1/2020	18
A01	North	30	8/1/2020	20
A01	North	30	9/1/2020	23
A01	North	30	10/1/2020	25
A01	North	30	11/1/2020	28
A01	North	30	12/1/2020	30

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

[Greg_Deckler]

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

[Newbie_2020]

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

Area	Location	FY Plan	Date	YTD Plan
A01	North	30	1/1/2020	3
A01	North	30	2/1/2020	5
A01	North	30	3/1/2020	8
A02	South	38	1/1/2020	6
A02	South	38	2/1/2020	10
A02	South	38	3/1/2020	13
A03	West	35	1/1/2020	3
A03	West	35	2/1/2020	6
A03	West	35	3/1/2020	9

Best,

newbie_2020

[Greg_Deckler · 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])

[Newbie_2020]

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

[Greg_Deckler]

Awesome! Glad we got there! 🙂