Max Value per ID by Currency - DAX Issue

sibe123 — Thu, 01 Jun 2023 18:55:33 GMT

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! 🙂

Re: Max Value per ID by Currency - DAX Issue

tamerj1 — Thu, 01 Jun 2023 19:39:30 GMT

Hi sibe123

please try

Current Amount =
SUMX (
VALUES ( Table1[ID] ),
MAXX (
TOPN ( 1, CALCULATETABLE ( Table2 ), Table2[Valid From] ),
Table2[Amount]
)
)

Re: Max Value per ID by Currency - DAX Issue

sibe123 — Fri, 02 Jun 2023 06:09:49 GMT

Thank you very much tamerj1 ! You are my todays hero :)!

topic Re: Max Value per ID by Currency - DAX Issue in DAX Commands and Tips

Max Value per ID by Currency - DAX Issue

Re: Max Value per ID by Currency - DAX Issue

Re: Max Value per ID by Currency - DAX Issue