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.
I'm having trouble trying to sum the most recent amount for accounts with multiple records.
Here is the table
The total amount I want from that table would be 60 (company A = 40 & Company B = 20)
I'm able to do it in excel using this array formula =MAX(IF($B$2:$B$7=B2,$A$2:$A$7))=A2 then filtering it out by only "TRUE." However, i'm having a hard time transititioning that formula to powerBi
Thanks in advance!
Solved! Go to Solution.
Hi @powerBIpeon ,
If you want to use the solution of Phil_Seamark, you need create another measure with the formula below.
Is Latest Row Filter = VAR LatestDate = MAXX ( FILTER ( 'Table1', 'Table1'[Account] = EARLIER ( 'Table1'[Account] ) ), 'Table1'[Date] ) RETURN IF ( 'Table1'[Date] = LatestDate, 1, 0 ) Measure = CALCULATE(SUM(Table1[Amount]),FILTER('Table1','Table1'[Is Latest Row Filter]=1))
In addition, you also could achieve your desired output with the formulas below.
Table = SUMMARIZE ( 'Table1', 'Table1'[Account], "date", CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[Account] ) ), "amount", MAX ( 'Table1'[Amount] ) ) Measure 2 = SUMX('Table',[amount])
Here is the output.
Best Regards,
Cherry
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @powerBIpeon ,
If you want to use the solution of Phil_Seamark, you need create another measure with the formula below.
Is Latest Row Filter = VAR LatestDate = MAXX ( FILTER ( 'Table1', 'Table1'[Account] = EARLIER ( 'Table1'[Account] ) ), 'Table1'[Date] ) RETURN IF ( 'Table1'[Date] = LatestDate, 1, 0 ) Measure = CALCULATE(SUM(Table1[Amount]),FILTER('Table1','Table1'[Is Latest Row Filter]=1))
In addition, you also could achieve your desired output with the formulas below.
Table = SUMMARIZE ( 'Table1', 'Table1'[Account], "date", CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[Account] ) ), "amount", MAX ( 'Table1'[Amount] ) ) Measure 2 = SUMX('Table',[amount])
Here is the output.
Best Regards,
Cherry
https://community.powerbi.com/t5/Desktop/Show-only-data-from-the-latest-date/td-p/442889
@Phil_Seamark I found this thread you answered which is what I'm looking for. I got it to work, but how would i add in a second critera?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |