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)

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
Miguel_Angel
Frequent Visitor
6 years ago

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-is/m-p/14826

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

 

  • 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

      • 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