cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## How to get value from last date (most recent) by category using a measure

Hello everybody,

I am new to PowerBI and have been trying to write a DAX formula to create a very simple measure to use in my visuals.

I want to know the latest value by category (topic & type!) whereas there are different dates for the different categories and the table is updated regularily (most recent date topic A, AC: 28.01.2023; most recent date Topic B, AC: 31.01.2023).

So what would for example be the specific formula for the value of

- the latest date (in here 28.01.2023)

- for topic A

- type AC ?

Thanks a lot for your help 🙂 !

1 ACCEPTED SOLUTION
Community Support

You can try the following methods.

``````Measure =
Var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Topic]="A"&&[Type]="AC"))
Return
CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),[Date]=_maxdate&&[Topic]="A"&&[Type]="AC"))``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Community Support

You can try the following methods.

``````Measure =
Var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Topic]="A"&&[Type]="AC"))
Return
CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),[Date]=_maxdate&&[Topic]="A"&&[Type]="AC"))``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Hi v-zhangti,

thanks a lot for your help - this is exactly what I was looking for!

Best,

CuriousMonkey

Regular Visitor

For the latest date for topic A I got

latestDate = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Topic]="A"))

And a sum of all values from topic A

_last_A =
CALCULATE(
SUM('Table'[Value]),
'Table'[Topic] IN { "A" }
)

But I cannot manage to find a formula to just give back the latest value for topic A & type AC 🙈
(and no, I donnot need a sum...I just need the single latest value)

Regular Visitor

Hi FreemanZ,

So what do I do exactly if I just want to get the latest value of Topic A, Type AC?

I just need a single value, not a list of all latest values by all categories.

Thanks again 🙂

Super User

try to plot a tabe visual with Topic, Type columns and a measure like:

``````LatestValue =
MAXX(
TOPN(
1,
TableName,
TableName[Date],
),
TableName[Value]
)``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors