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

 

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