Forum Discussion

Miguel_Angel's avatar
Miguel_Angel
Frequent Visitor
6 years ago
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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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)

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Could you share a simple table and show the expected result?

Miguel_Angel's avatar
Miguel_Angel
Frequent Visitor
6 years ago

Hi Ashish_Mathur, here is the capture of data table (source):

 

 

For example, if i select in the data slicer, 2019-11-20, under the rules i have detailed:

 

- Entry date <= Max date slicer (max register date)

- Termination date> Max date slicer (max register date)

- Select unique ID with the max register date, always th register date<= Max date slicer

 

The result should be:

Thank you for your time. Best regards