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

[CuriousMonkey]

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 🙂 !

[v-zhangti · Accepted solution]

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

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.

[CuriousMonkey]

Hi v-zhangti,

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

Best,

CuriousMonkey

PREVIEW

[CuriousMonkey]

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]

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]

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