Forum Discussion

ChristianTD's avatar
ChristianTD
Frequent Visitor
8 years ago
Solved

Show only data from the latest date

Dear all, I have a very basic question. I constantly struggle with showing only the data with the newest date. What is the easiest way to get a visual only to show data from the most recent date.  ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi ChristianTD,

     

    Seems date type values don't have a "TOP" filter type. I would suggest you try the measure below and add it to the "Visual Level filter". Don't need to add it to any visual. Then filter the filter as "1". Please give it a try.

    Measure =
VAR LatestDate =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'table1' ) )
RETURN
    IF ( MIN ( 'Table1'[Date] ) = LatestDate, 1, 0 )

    Best Regards,

    Dale

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

One way is to add this calculated column to your table, which returns a 1 or 0 for latest/not latest which you can use as a filter

 

Is Latest Row Filter = 
VAR LatestDate = MAXX(FILTER('Table1','Table1'[Type] = EARLIER('Table1'[Type])),'Table1'[Date])
RETURN IF('Table1'[Date]=LatestDate,1,0)
  • ChristianTD's avatar
    ChristianTD
    Frequent Visitor
    8 years ago

    Thanks.

     

    You would then apply a filter where this column is = 1, wouldn't you have to display this column in order for it to work?

    would this be updated every time I do a refresh of data ?

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago
      Correct. Just add it as a filter on your visual ser to 1
      • ChristianTD's avatar
        ChristianTD
        Frequent Visitor
        8 years ago

        I would like not to show the new column.

        I also tried with a measure Latest = LOOKUPVALUE(Table[Date];Table[Date];MAX(Table[Date])) and then adding my date to the filter and add top 1 by Latest.

         

        Alternatively wouldn't just a simple filter work.. date top 1 by date?

  • SachinSawant's avatar
    SachinSawant
    Regular Visitor
    7 years ago

    Hi Phil,

     

    When i try to enter the function you suggested, i am getting "Token eof expected" error, cant figure out why? Could you help Please.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This worked for me thanks:)

  • raphazzz's avatar
    raphazzz
    Icon for Helper I rankHelper I
    3 years ago

    I came accross your proposed solution but it is returning 1 for either the lastest or oldest submission...

    Update = 
VAR Last =
    MAXX(
        FILTER ( 'Sales', 'Sales'[submission_date] = EARLIER ( 'Sales'[submission_date] ) ),
        'Sales'[submission_date]
    )
RETURN
    IF ( 'Sales'[submission_date] = Last, 1, 0 )


    These are results for the same branch, this table has 9 variables but the only the brach name is the same amd projection month have the same data.