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)

     

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

Miguel_Angel's avatar
Miguel_Angel
Frequent Visitor
6 years ago

 I think i have the solution. The steps: (in this order)

- Sort by ID Column

- Sort by register date

- Sort by termination date

- Create a calculate column: 

later_id = CALCULATE(

MAX(PRODUCCION[ID]);

FILTER(PRODUCCION;PRODUCCION[Index]=EARLIER(PRODUCCION[Index])+1)

)

- Create a calculate column:

later_register_date = CALCULATE(

MAX(PRODUCCION[Register date]);

FILTER(PRODUCCION;PRODUCCION[Index]=EARLIER(PRODUCCION[Index])+1)

)

- Create a measure:

Status_final = var previous_status=if(

max(PRODUCCION[Register date])<=max(CALENDARIO[Date]);

if (or(max(PRODUCCION[Termination date])>max(CALENDARIO[Date]);

ISBLANK(max(PRODUCCION[Termination date])));

1;0))

return

if (previous_status=1;

if (max(PRODUCCION[ID])=max(PRODUCCION[later_id]) &&

max(PRODUCCION[later_register_date])<=max(CALENDARIO[Date]);

0;1))

- The result:

Thanks to all of you who have helped me. Best regards