Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
FredBus
Frequent Visitor

Outstanding Amount summed up on each day with multiple criteria

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:

 

FredBus_0-1681384250676.png

 

 

1 ACCEPTED SOLUTION

@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]
)
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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]
)
)

@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:

FredBus_0-1681395513665.png

My filterd Table by the slicer should look like this:

FredBus_1-1681395809690.png

 

if I change the filter to the following date:

FredBus_2-1681395910746.png

My table should look like this:

 

FredBus_3-1681396195005.png

 

 

 

@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]
)
)

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.