Get First and Last Values from Table

[GilbertQ]

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

[v-yulgu-msft]

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]

Hi there

thanks for that I will see what i can do.

[Vvelarde]

@GilbertQ

you can try with:

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

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

[GilbertQ · Accepted solution]

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

[Habib]

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]

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