Second Last Date Total

[Boycie92]

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

[amitchandak · Accepted solution]

@Boycie92 

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

[Boycie92]

Hi @amitchandak 

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

Thanks,

Boycie92

[amitchandak]

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

[Fowmy]

@Boycie92 

Please check this solution, 

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