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 Aiolos, i tried to simplify the problem, but it's more complex.

More variables enter and the solution. 

I have 3 column date:

- entry date

- termination date

- register date

 

The slicer date, depend of register date. I need, a table, show last value by id, under this rules:

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

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

- In the repeated ID (For example ID 7),  show only (unique), the register with the max Register date, always Register date< Max data slicer. For example, if i have select in the date slicer: 2019-11-20, the table should show (For ID 7):

 

 

for the sum of the total values,  i have tried summarize function, and it doesn't work for me:

 

Thank you for your time

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Miguel_Angel ,

 

I think you can use the max() to get the value. For example:

 

measure = calculate(max(value),filter(all(termination date, register date),termination date > max(register date) && entry date < max(register date)))

 

and create a slicer for register date, and use the equal or less than mode.

 

Please try.

Aiolos Zhao