Forum Discussion
Solved
Select a value based on max date
Hi, I have the following table: I need the table show the value from lastest date of the CodigoContrato field, and no show (or value Consumo 0) of the repeated record (crossed in red). The...
Hi Miguel_Angel
Create a date table which is isolate from other tables, then use the date column in a slicer.
Then create measures
ss
Measure = IF ( MAX ( 'Table 3'[Entry date] ) <= MAX ( 'Date'[Date] ) && ( MAX ( 'Table 3'[Termination Date] ) >= MAX ( 'Date'[Date] ) || MAX ( 'Table 3'[Termination Date] ) = BLANK () ) && MAX ( 'Table 3'[Register date] ) <= MAX ( 'Date'[Date] ), 1, 0 ) Measure 2 = IF ( [Measure] = 1, RANKX ( FILTER ( ALLEXCEPT ( 'Table 3', 'Table 3'[ID] ), [Measure] = 1 ), CALCULATE ( MAX ( [Register date] ) ), , DESC, DENSE ) ) Measure 3 = IF([Measure]=1&&[Measure 2]=1,1,0)
Hi Miguel_Angel
Create a date table which is isolate from other tables, then use the date column in a slicer.
Then create measures
ss
Measure =
IF (
MAX ( 'Table 3'[Entry date] ) <= MAX ( 'Date'[Date] )
&& (
MAX ( 'Table 3'[Termination Date] ) >= MAX ( 'Date'[Date] )
|| MAX ( 'Table 3'[Termination Date] ) = BLANK ()
)
&& MAX ( 'Table 3'[Register date] ) <= MAX ( 'Date'[Date] ),
1,
0
)
Measure 2 =
IF (
[Measure] = 1,
RANKX (
FILTER ( ALLEXCEPT ( 'Table 3', 'Table 3'[ID] ), [Measure] = 1 ),
CALCULATE ( MAX ( [Register date] ) ),
,
DESC,
DENSE
)
)
Measure 3 = IF([Measure]=1&&[Measure 2]=1,1,0)
Hi v-juanli-msft , your solution works!!. The result is the same as mine:
But your solution, is more elegant. Thank you very much for the effort. Whenever you want, you are invited to a few beers in Málaga (Spain)