cancel
Showing results for
Did you mean:  Helper II

## Calculated Measure

I have a table like this :

 Date Num return 22/1/2022 14 21/1/2022 20 21/1/2022 15 20/1/2022 20

How do I create a measure that shows the number of return for the most recent date.

The measure should have a value of 35 on 21/2022 and 14 on 22/1/2022

2 ACCEPTED SOLUTIONS  Super User

@gancw1 , Most recent or two most recent, based on selection or over all

You can try like

measure =

var _max = maxx(allselected(Table), Table[Date])

return

calculate(sum(Table[Num return]), filter(Table, Table[Date] =_max))  Community Support

Hi @gancw1 ,

• Condition1: Every date has Num:

You could simply use the "Sum" type of summarization for [Num Return] Or use :

``Measure = CALCULATE(SUM('Table'[Num return]),ALLEXCEPT('Table','Table'[Date]))`` • Condition2: There are blank values in Table:

Based on the keyword ——" the most recent date", I used another table by adding 1/24/2022 Since 1/24/2022 has no value ,it should get sum from the most recent date=2022/1/22  =14

``````Measure2 =
var _max= MAXX(FILTER(ALL('Table (2)'),[Date]<=MAX('Table (2)'[Date]) && [Num return]<>BLANK()  ),[Date])
return CALCULATE(SUM('Table (2)'[Num return]),FILTER(ALL('Table (2)'),[Date]=_max))``````

Output: Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2  Community Support

Hi @gancw1 ,

• Condition1: Every date has Num:

You could simply use the "Sum" type of summarization for [Num Return] Or use :

``Measure = CALCULATE(SUM('Table'[Num return]),ALLEXCEPT('Table','Table'[Date]))`` • Condition2: There are blank values in Table:

Based on the keyword ——" the most recent date", I used another table by adding 1/24/2022 Since 1/24/2022 has no value ,it should get sum from the most recent date=2022/1/22  =14

``````Measure2 =
var _max= MAXX(FILTER(ALL('Table (2)'),[Date]<=MAX('Table (2)'[Date]) && [Num return]<>BLANK()  ),[Date])
return CALCULATE(SUM('Table (2)'[Num return]),FILTER(ALL('Table (2)'),[Date]=_max))``````

Output: Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User

@gancw1 , Most recent or two most recent, based on selection or over all

You can try like

measure =

var _max = maxx(allselected(Table), Table[Date])

return

calculate(sum(Table[Num return]), filter(Table, Table[Date] =_max))  