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). There is a time slicer that can change the table. For example, if change the date in slicer, i have the table: I have tried in many ways, with no results.Any help will be appreciated

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)

Select a value based on max date | Microsoft Fabric Community

10 Replies

v-juanli-msft

Community Support

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

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
Frequent Visitor
6 years ago
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)

Anonymous
Not applicable
6 years ago
Hi Miguel_Angel ,

Create a measure like below:

measure = max(FechaRegistro)

Then using the measure to replace the second column.

If it's not what you want, please share your sample and clarify your question.

Thanks.

Aiolos Zhao
- 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
    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