Forum Discussion
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.
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])
5 Replies
- Greg_Deckler
Community Champion
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])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
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])
