Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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])
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
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])
@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! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |