Select a value based on max date

[Miguel_Angel]

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

[v-juanli-msft · Accepted solution]

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]

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]

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]

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

[v-juanli-msft]

Hi @Miguel_Angel 

Please create another table using 

new table=Values(table[register date])

This new table has no relationship with others.

Then add [register date] from "new table" into a slicer,

Create a measure

max date=max('new table'[register date])

To only show rows which meets the rule.

create a measure

meet_rule =
IF (
    MAX ( table[Entry date] ) < [max date]
        && (
            MAX ( table[Termination date] ) > [max date]
                || MAX ( table[Termination date] ) = BLANK ()
        ),
    "should show",
    "not show"
)

Then add this measure into a table visual level filter, apply the filter to show rows you'd like.

If you want to sum the values which meets rules.

Create a measure

sum =
CALCULATE (
    SUM ( [values] ),
    FILTER (
        table,
        table[Entry date] < [max date]
            && (
                table[Termination date] > [max date]
                    || table[Termination date] = BLANK ()
            )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Miguel_Angel]

Hi @Maggie, the solution is near. But if i enter a new column in table, it duplicate ID. Oonly should show the ID (unique) with the Max Register date :

If I don't add any column (I leave only ID and values) if it works:

The sum doesn't works because duplicate some values for ID.  I'm thinking if rankx could be a solution.

Here, is a similar case:  https://community.powerbi.com/t5/Desktop/Finding-the-max-date-row-between-date-range-and-date-range-...

Thanks @Anonymous for your last contribution, but it doesn't works. Best regards

[Ashish_Mathur]

Hi,

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

[Miguel_Angel]

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]

 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

[Anonymous]

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