topic Re: Outstanding Amount summed up on each day with multiple criteria in DAX Commands and Tips

Outstanding Amount summed up on each day with multiple criteria

FredBus — Thu, 13 Apr 2023 11:34:16 GMT

Hi there,

i am trying to create a measure which will calculate the sum of the outstandig amount on each day. The Measure should be filtered through a date slicer.
A Document No_ can appear multiple times with different Version_No_.
I need the summed up value (from the document No_ with the highest Version No_) for each day.

I have tried the following measure :

sum Amount =

VAR _id= Max('Outstanding Amount archieved'[ID])

VAR _date= CALCULATE(Max('Outstanding Amount archieved'[Date achieved]),ALLSELECTED('Outstanding Amount archieved'),'Outstanding Amount archieved'[ID]= _id)

Return

Calculate(sum('Outstanding Amount archieved'[Amount open]), VALUES('Outstanding Amount archieved'),'Outstanding Amount archieved'[ID]=_id,'Outstanding Amount archieved'[Date achieved]=_date)

See below an example of the data:

Re: Outstanding Amount summed up on each day with multiple criteria

tamerj1 — Thu, 13 Apr 2023 13:09:08 GMT

Hi FredBus

please try

sum Amount =
SUMX (
SUMMARIZE (
'Outstanding Amount archieved',
'Outstanding Amount archieved'[Drawing No.],
'Outstanding Amount archieved'[Date achieved]
),
SUMX (
TOPN (
1,
CALCULATETABLE ( 'Outstanding Amount archieved' ),
'Outstanding Amount archieved'[Version No.]
),
'Outstanding Amount archieved'[Amount open]
)
)

Re: Outstanding Amount summed up on each day with multiple criteria

FredBus — Thu, 13 Apr 2023 14:47:33 GMT

tamerj1thanks a lot for your reply.
I think I have expressed myself incorrectly.
Your measure sums up all values with the highest version No_ for each day and thus a Document No. occurs several times. But I need only the value for the most recent date depending on my date filter.

If no date is filtered, I need the most recent summed values, but the document no. must occur only once.

My Date Slicer:

My filterd Table by the slicer should look like this:

if I change the filter to the following date:

My table should look like this:

Re: Outstanding Amount summed up on each day with multiple criteria

tamerj1 — Thu, 13 Apr 2023 16:28:37 GMT

FredBus

Please try

sum Amount =
SUMX (
VALUES ( 'Outstanding Amount archieved'[Drawing No.] ),
SUMX (
TOPN (
1,
TOPN (
1,
CALCULATETABLE ( 'Outstanding Amount archieved' ),
'Outstanding Amount archieved'[Version No.]
),
'Outstanding Amount archieved'[Date achieved]
),
'Outstanding Amount archieved'[Amount open]
)
)

Re: Outstanding Amount summed up on each day with multiple criteria

FredBus — Thu, 13 Apr 2023 19:49:20 GMT

tamerj1 You have made it. Thanks a lot for your help.