The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Solved! Go to Solution.
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]
)
)
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:
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:
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]
)
)
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
15 | |
12 | |
12 | |
7 |