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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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.