cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Second Last Date Total

Hi,

I am hoping someone can help I have a very simple table.

I would like to be able to create a measure that would provide the figure (total) for the second last date available in my data. So, in this scenario 400. Any ideas?

Date                  Total

1/1/2020           100

2/1/2020           150

3/1/2020           400

4/1/2020           500

Thanks,

Boycie92

1 ACCEPTED SOLUTION
Super User

measure =
var _max= maxx(allselected(Table),Table[date])
return
calculate(lastnonblankvalue(Table[date],Sum(Table[Total])),Table[Date]<_max)

OR

measure =
var _max= maxx(allselected(Table),Table[date])
return
calculate(lastnonblankvalue(Table[date],Sum(Table[Total])),filter(allselected(Table),Table[Date]<_max))

4 REPLIES 4
Super User

measure =
var _max= maxx(allselected(Table),Table[date])
return
calculate(lastnonblankvalue(Table[date],Sum(Table[Total])),Table[Date]<_max)

OR

measure =
var _max= maxx(allselected(Table),Table[date])
return
calculate(lastnonblankvalue(Table[date],Sum(Table[Total])),filter(allselected(Table),Table[Date]<_max))

Resolver I

That works perfectly thank yo so much. Is there anyway to display the second las date as a seperate messure?

Thanks,

Boycie92

Super User

@Boycie92 , Create a measure in the same way as the last one and  in that use

max(Table[Date]) in place of Sum(Table[Total])

Super User

@Boycie92

https://community.powerbi.com/t5/Desktop/Show-second-last-date-value-in-card/td-p/531093

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors