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 ass...
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]))

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]))

      • GilbertQ's avatar
        GilbertQ
        Super User
        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]))