I need a help on a simple DAX measure

[Anonymous]

Hello everybody,

I'm looking for a DAX measure that calculates this :

Person    Amount      Week
1011            10$            1
1011            15$            2

1011            18$            3

1011            11$            4
1012             9$             1  
1012             7$             2
1013             3$             1
1013             4$             2
1013             8$             3

I need the SUM of Amounts of the last week of each person :

11$ +  7$  + 8$ = 26$

Any helps ?

Thank you

[petrovnikitamai · Accepted solution]

i'm not sure but try to add 

1. new column 

max_Week_by_Person = maxx(filter([TableName];earlier([Person])=[Person]);[Week])

2. new mesuare

sum_Amount_byLastWeek = sumx(filter(TableName;[Week]=[max_Week_by_Person]);[Amount])

mb someone knows best way to get it

[v-piga-msft]

Hi @Anonymous,

By my tests, the formula from petrovnikitamai and LivioLanzo should solve your problem. 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[LivioLanzo · Accepted solution]

Measure = 
SUMX(
    NATURALINNERJOIN(
        Data,
        ADDCOLUMNS(
            VALUES( Data[Person] ),
            "MaxWeek", CALCULATE( MAX( Data[Week] ) )
        )
    ),
    [Amount] * INT( [MaxWeek] = [Week] )
)

 an alternative approach

[petrovnikitamai · Accepted solution]

i'm not sure but try to add 

1. new column 

max_Week_by_Person = maxx(filter([TableName];earlier([Person])=[Person]);[Week])

2. new mesuare

sum_Amount_byLastWeek = sumx(filter(TableName;[Week]=[max_Week_by_Person]);[Amount])

mb someone knows best way to get it

[Anonymous]

See sample

https://ufile.io/yipno