Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How To display Latest Records for the same date

I Have the report with the many visuals and the sample data is attached below.

Here we have one file and it is loaded in to the table from excel file. everyday it is uploaded by different times. for each file we are giving batch id.

Batch id DateTable NameSuccessful records
13/12/2019Landing Table10
23/12/2019Landing Table5
33/12/2019Landing Table6
14/12/2019Landing Table9
24/12/2019Landing Table10
15/12/2019Landing Table6
25/12/2019Landing Table4
35/12/2019Landing Table2

Now we are reporting this any different visuals. with the date filter. Now the user want to see the latest data for the current date.That is only the below data.

35/12/2019Landing Table2

But the user also should have the flexibility to see the other date data by using filter.

 

Please help me on this issue.

Show and Tell
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Firstly, we need to add a indexcolumn in Power Query Editor to identify the latest rows of same day.

     

     

    Then we can create a measure and use it in Visual Filter to meet your requirement:

     

    IsLatestRecordOfMaxDay =
VAR maxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) = maxDate
            && SELECTEDVALUE ( 'Table'[Index] )
                = CALCULATE (
                    MAX ( 'Table'[Index] ),
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = maxDate )
                ),
        1,
        -1
    )

     

    The date does not nessary to sort by increament, we create index column to get the latest record after filter the table with the latest day.

     

     


    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


    Best regards,

     

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Anonymous ,

     

    Use the slicer with Relative option. Screen shot is attached.

     

    Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Tahreem24,

       

      The solution that you provided is not working for me. Because here i have tye dependency with Batchid also.

      For the current date the latest batchid file need to be display in the reports but in the filter we should have all the values to select.

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Firstly, we need to add a indexcolumn in Power Query Editor to identify the latest rows of same day.

         

         

        Then we can create a measure and use it in Visual Filter to meet your requirement:

         

        IsLatestRecordOfMaxDay =
VAR maxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) = maxDate
            && SELECTEDVALUE ( 'Table'[Index] )
                = CALCULATE (
                    MAX ( 'Table'[Index] ),
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = maxDate )
                ),
        1,
        -1
    )

         

        The date does not nessary to sort by increament, we create index column to get the latest record after filter the table with the latest day.

         

         


        If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


        Best regards,

         

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,