Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using a slicer to compare two rows in the same table

I have a series of records with unique IDs, and I'm creating monthly snapshots of these records with a generic text description field in place. Something like:   Record Date Comments A Mar...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous

     

    Thank you for the explanation. Based on the sample data, you can create below measure.

    Current Comment = 
VAR maxDate = CALCULATE(MAX(Record_Table[Date]),ALL(Record_Table))
VAR currentComment = CALCULATE(MAX(Record_Table[Comments]),ALLEXCEPT(Record_Table,Record_Table[Record]),Record_Table[Date]=maxDate)
VAR initialComment = SELECTEDVALUE(Record_Table[Comments])
RETURN
IF(currentComment = initialComment,BLANK(),currentComment)

     

    Put Date column into a slicer. Put Record, Comments and above measure into a table visual. Comments column will show comments on a selected initial date. You can rename Comments column for this table visual specificially. 

    Let me know if you have any questions.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous 

 

I have two questions, can you help me understand them better? Currently in your sample table, the latest date for both records is September 20, 2021. Is it possible that different records have different latest record date? And when a user selected an initial date, is it possible that a record doesn't have a record on that day but it has one before that day, so we need to count the latest record earlier than or equal to the initial day?

 

Best Regards,
Community Support Team _ Jing

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi, and thanks for the questions! For my specific example, I am most interested "what is the latest snapshot of information". So there will be one latest record date. Generically it would be nice to be able to be dynamic with both beginning and end dates, but I'm satisfied for my purposes to only worry about a dynamic start date.

 

With regards to your second question, if there is no record for my selected "initial" date, I'm happy to ignore it. With the data I'm using, records persist. So, if it wasn't there on July 3, for example, it's also not going to be present at an earlier date.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous

     

    Thank you for the explanation. Based on the sample data, you can create below measure.

    Current Comment = 
VAR maxDate = CALCULATE(MAX(Record_Table[Date]),ALL(Record_Table))
VAR currentComment = CALCULATE(MAX(Record_Table[Comments]),ALLEXCEPT(Record_Table,Record_Table[Record]),Record_Table[Date]=maxDate)
VAR initialComment = SELECTEDVALUE(Record_Table[Comments])
RETURN
IF(currentComment = initialComment,BLANK(),currentComment)

     

    Put Date column into a slicer. Put Record, Comments and above measure into a table visual. Comments column will show comments on a selected initial date. You can rename Comments column for this table visual specificially. 

    Let me know if you have any questions.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.