Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
CuriousMonkey
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).

 

CuriousMonkey_0-1675227584851.png


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
v-zhangti
Community Support
Community Support

Hi, @CuriousMonkey 

 

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

vzhangti_0-1675318811186.png

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.

View solution in original post

5 REPLIES 5
v-zhangti
Community Support
Community Support

Hi, @CuriousMonkey 

 

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

vzhangti_0-1675318811186.png

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.

Hi v-zhangti,

 

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

 

Best,

CuriousMonkey

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)

Thanks for your help!
CuriousMonkey
Regular Visitor

Hi FreemanZ,

 

thanks a lot for your reply!

 

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 🙂

FreemanZ
Super User
Super User

hi @CuriousMonkey 

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

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

 

FreemanZ_0-1675232177400.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.