topic How to get a value from another table based on the max date selected in a filter? in DAX Commands and Tips

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

Newbie_2020 — Sun, 08 Mar 2020 00:19:41 GMT

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

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

Greg_Deckler — Sun, 08 Mar 2020 00:45:31 GMT

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

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

Newbie_2020 — Sun, 08 Mar 2020 19:17:42 GMT

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

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

Greg_Deckler — Sun, 08 Mar 2020 19:30:03 GMT

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

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

Newbie_2020 — Sun, 08 Mar 2020 21:02:27 GMT

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

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

Greg_Deckler — Sun, 08 Mar 2020 21:28:33 GMT

Awesome! Glad we got there! 🙂