Show Category for a MAX date

[okusai3000]

Hello everyone, 

I am trying to get the last "Aprt_estado" of the last date from this articles. On this example, for my filter of February 2020, I I woud like to see for the ESAP_COD 81029, only 28/02/20 and aprt estado= ET. That's it.  If move out the column "aprt_estado", my measure will show the correct last date (28/02) but I need that information.

Any advice? thanks a lot!!

[v-yuaj-msft · Accepted solution]

Hi @okusai3000 ,

Based on your description, you can do some steps as follows.

1. Create a calculated index column.

index =

RANKX (

    FILTER ('CASE2',

'CASE2'[esca_cod]=EARLIER('CASE2'[esca_cod])),

'CASE2'[Ultimo Mov].[Date],

,DESC

)

1. Create a measure.

Measure = CALCULATE(MAX('CASE2'[esca_cod]),'CASE2'[index]=1)

Result:

Best Regards,

Yuna

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

[amitchandak]

@okusai3000 , add new measures like

lastnonblankvalue(table[Ultimo Mov] ,max(Table[Aprt_estado]))

lastnonblankvalue(table[Ultimo Mov] ,max(Table[Cant Equipos V2]))

[okusai3000]

hi @amitchandak ,

The problem is that *Ultimo Mov is not a column... is a Measure, so I cannot use it on lastnonblankvalue... any other idea?

thanks!

BTW, this is [ultimo mov]

*Ultimo Mov =

VAR CurrentDate = (max('Dim Date'[Date]))

VAR Results =

CALCULATE(LASTDATE('Fact Stock Equipos'[fecha]),

filter(all('Dim Date'),

('Dim Date'[Date] <= CurrentDate)),USERELATIONSHIP('Dim Date'[Date],'Fact Stock Equipos'[fecha]))

RETURN

Results

EDIT.

Ok, finally solved with a part of what @amitchandak said,

*aprt_estado =

VAR CurrentDate = (max('Dim Date'[Date]))

VAR Results =

CALCULATE(LASTNONBLANKVALUE('Dim Date'[Date],max('Fact Stock Equipos'[aprt_estado])),

filter(all('Dim Date'),

('Dim Date'[Date] <= CurrentDate)),all('Dim Tipos de Estado'),USERELATIONSHIP('Dim Date'[Date],'Fact Stock Equipos'[fecha]))

RETURN

Results

thanks!