topic Re: Return SUM for newest value by unique ID in DAX Commands and Tips

Return SUM for newest value by unique ID

liamr0639 — Fri, 27 Oct 2023 08:37:00 GMT

Hi all,

I'm pretty new to PowerBi but I have got good familarity.

I want to return a SUM value (so I can put in a card visual) of a SUM of values only by unique IDs and their latest date of values.

This means I can put a date slider which filters the latest SUM values (by unique IDs) in a date range.

Example data:

ID	Amount	DateTime
1	10	01/01/2023
1	5	02/05/2023
1	2	01/08/2023
2	15	01/01/2023
2	10	02/05/2023
2	5	01/08/2023

I want to then add a filter slider and if I select January and it only shows the SUM value of 25. But if I change the slider for May it will show me the SUM value of 15. If I set the date slider for the whole of 2023 I want it to only show the latest data which is a SUM of 7.

I can create DAX to SUM latest values only but if I change the slider to January there is no data. So I need to be able to create a SUM based on unique IDs only and their latest date based on a filter slider.

Many thanks,

Liam

Re: Return SUM for newest value by unique ID

ValtteriN — Fri, 27 Oct 2023 09:30:07 GMT

Hi,

Here is one way to do this:

Latest or sum =

var _latest = CALCULATE(Month(MAX('Table (11)'[DateTime])),ALL('Table (11)')) return

IF(HASONEFILTER('Calendar'[Month]),SUM('Table (11)'[Amount]),CALCULATE(SUM('Table (11)'[Amount]),ALL('Table (11)'[DateTime]),MONTH('Table (11)'[DateTime])=_latest))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Return SUM for newest value by unique ID

liamr0639 — Fri, 27 Oct 2023 09:34:50 GMT

Hi, many thanks for this!

Do you know if it's possible to do by day also? As I want the slider to be in days between a period.

Many thanks

Re: Return SUM for newest value by unique ID

ValtteriN — Fri, 27 Oct 2023 10:02:28 GMT

Sure, something like this might work depending on the filters you use:

Latest or sum =

var _latest = CALCULATE(Month(MAX('Table (11)'[DateTime])),ALL('Table (11)'))

var _latestS = MAX('Calendar'[Month])

return

IF(COUNTROWS(FILTER(ALL('Table (11)'),MONTH('Table (11)'[DateTime])=_latestS))=0,

CALCULATE(SUM('Table (11)'[Amount]),ALL('Table (11)'[DateTime]),MONTH('Table (11)'[DateTime]))=_latest,SUM('Table (11)'[Amount]))