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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors