Set Analysis

[alec1982]

Hi guys,

I need to do Sum amount of the most recent date. The below formula sums everything in the past. Is there away to fix to show only the most recent period amount?

=Sum({$<[Date]={"<$(=today())"},[Type]={'AR'}>}[Amount])

[maxgro]

what do you mean for

the most recent period

?

[jolivares]

Change to :

=Sum({$<[Date]={">=$(=today())"},[Type]={'AR'}>}[Amount])

Your expression sum all amount before today, changing the bracket you sum from today to....

[alec1982]

I want to sum amounts paid in the past or today but only one line(Most Recent).

Date               Amount

05/01/2014     100

06/01/2014     250

06/07/2014     500

07/01/2014     220

in this case the result should be 250

[alec1982]

here is the formula to calculate the desired date.. but not sure how to get the amount

date(max({$<[Payment Date]={"<=$(=today())"}>}[Payment Date]))

[alec1982]

most recent means the last Amount scheduled today or prior to today's date.

[maxgro · Accepted solution]

maybe

=FirstSortedValue({$<Date={"<$(=today())"}>} Amount, -Date)

or

=subfield(concat(aggr(sum({$<Date={"<$(=today())"}>} Amount),Date), '-', -Date), '-',1)