Hello!
I want to determine a value (Amount) based on the maximum date (Valid From). The measure should be exerminable per ID and per Currency.
Table 1:
| ID | Name | Currency |
| 1 | Example 1 | EUR |
| 2 | Example 2 | USD |
| 3 | Example 3 | EUR |
Table 2:
| ID | Valid From | Amount |
| 1 | 01.01.2010 | 10 |
| 1 | 01.01.2017 | 20 |
| 1 | 01.01.2020 | 30 |
| 2 | 01.01.2010 | 15 |
| 2 | 01.05.2017 | 25 |
| 3 | 01.01.2010 | 8 |
Tables are connected in 1:n via ID.
I have two visuals, one per Currency (Table 1) and one per ID (Table 1/2). Ideally I want to use the same measure to get: the "current" Amount of Table 2.
So if the Slicer is set to e.g. 01.04.2017 I want to Have:
Visual 1 (per ID):
- For ID 1: 20
- For ID 2: 15
- For ID 3: 8
and Visual 2 (per Currency):
- For EUR the Sum of 1 + 3 (both EUR): 28
- For USD the Sum of 2: 15
I was able to create following measure in Table 2:
Current Amount =
VAR TargetDate = MAX(table2[Valid From])
RETURN
CALCULATE(MAX(table2[Amount]),FILTER(table2,table2[Valid From] = TargetDate))
This works for Visual 1, but not for 2. I dont know how I should sum up. I tried SUMX, DISTINCT, VALUES, and so on ... but I'm not lucky.
Is it possible to create one measure (or at least two) to achieve this?
Thank you very much! 🙂
