cancel
Showing results for
Did you mean:
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

 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

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
5 REPLIES 5
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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Frequent Visitor

Hello @Greg_Deckler,

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Frequent Visitor

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

Super User

Awesome! Glad we got there! 🙂

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements