Forum Discussion

GilbertQ's avatar
GilbertQ
Super User
9 years ago
Solved

Get First and Last Values from Table

I was wondering if someone would help me with the following.

 

I have a table that has got Dates and the Amount.

 

What I want to return is the first and last date, with the corresponding (or associated) amount. This could potentially be the first and last values, instead of the dates.

 

I know that you can potentially do this with the filters, but it is taking a really long time to run.


thanks

Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    Hi there

     

    Thanks for that what I did was the following solution which worked for me due to having using a Date Table, which ensured that I got the last actual value and first actual value.

     

    Sales YOY - First Date = CALCULATE([Sales],FIRSTNONBLANK('Date'[Fiscal Year],'Sales Year on Year'[TSales]))

    Sales YOY - Last Date = CALCULATE([Sales],LASTNONBLANK('Date'[Fiscal Year],'Sales Year on Year'[TSales]))

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi GilbertQ

     

    I suppose that your dataset contains large data which makes it spend much time to filter. But it seems that using filter to fetch the records corresponding to the first and last date is the only option currently. Based on my research, I cannot find any solution to improve the performance of running filter.

     

    I think you can create a calculate table that contains only the first and last date with the corresponding amount like a cache so that you don't need to run the filter each time.

     

    Best regards,
    Yuliana Gu

    • GilbertQ's avatar
      GilbertQ
      Super User
      9 years ago

      Hi there


      thanks for that I will see what i can do.

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        GilbertQ

         

        you can try with:

         

        FirstDate-Value = CALCULATE(VALUES(Table1[Value]),FIRSTDATE(Table1[Date]))

        LastDate-Value = CALCULATE(VALUES(Table1[Value]),LASTDATE(Table1[Date]))

  • Habib's avatar
    Habib
    Continued Contributor
    9 years ago

    Filter seems the only option as you need to get the first/last date and then use filter to get the amount for that specific date.

     

     

    • GilbertQ's avatar
      GilbertQ
      Super User
      9 years ago

      Thanks, I was hoping for a solution with a function!