Show last value of column

[VictorV]

Hi everyone,

I have table below, and the month filter based on it. I want create a card visual, that it will appear the last Balance whenever I select a month.

For example: If I choose Month "1" in slicer, the card visual should show 3000, and if I select Month "2" it will show 5000, etc.

And if it not select any month, it will show the last value, in this case is 6000.

And also, another card visual, that will show the last month balance

For example: If I select month "2", it will show month 1 last balance, in this case is "3000".

Please help me, thanks!

[v-huijiey-msft]

Hi @VictorV ,

Thanks for the reply from @MattAllington , please allow me to provide another insight:

First, create a calculated column to extract the month of the Date column and put it in the Slicer.

Month = 'Table'[Date].[MonthNo]

Secondly, create two measures to achieve your needs:

Last Balance =
VAR SelectedMonth = MAX('Table'[Month])
RETURN
CALCULATE(LASTNONBLANK('Table'[Balance], 0), 'Table'[Month] = SelectedMonth)

Previous Month Last Balance =
VAR SelectedMonth = MAX('Table'[Month]) - 1
RETURN
CALCULATE(LASTNONBLANK('Table'[Balance], 0), 'Table'[Month] = SelectedMonth)

Place the two measures into the card visual object respectively, and the final page effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[VictorV]

Thanks for your replied, It work for "Last Balance". But for the "Previous Month Last Balance" It not getting the last value of last month, it get max value instead. And sometimes the last value in "balance" is not the highest value.

However for "Last balance", I use this code instead, I don't know if it would work for "Previous Month Last Blance" or not 

Last Value = CALCULATE(MAX('Table'[Balance]), FILTER('Table','Table'[Index] = MAX('Table'[Index])))

[v-huijiey-msft · Accepted solution]

Hi @VictorV ,

Please try the modified measures:

Last Balance = 
var _a = LASTNONBLANK('Table'[Index], 0)
RETURN
CALCULATE(MAX('Table'[Balance]),'Table'[Index]=_a)

Previous Month Last Balance = 
VAR __max_month = MAX('Table'[Month])
VAR __max_date_prev = CALCULATE(MAX('Table'[Date]),'Table'[Month]<__max_month)
var tmp = FILTER(ALL('Table'),[Date]=__max_date_prev)
var max_index = MAXX(tmp,[Index])
RETURN
CALCULATE(MAX('Table'[Balance]),FILTER(ALL('Table'),[Index]=max_index))

The visual effect of the page is as shown below:

The pbix file has been attached.

If you have other questions, please contact me at any time.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[VictorV]

Thanks a lot! it's work perfect for me.

[VictorV]

Is there a way to do this without create calendar table?

[MattAllington]

Not without nasty DAX in calculated columns and measures. Even then, I couldn't guarantee it would always return the correct answer. 

I have a friend that used to write all his letters using Excel. He never leant to use Word, even though that was the right way to do it. In the same way, I guess you could solve the problem using the wrong approach, and rankly there's probably someone on this forum that will show you how to do it the wrong way, but I wouldn't recommend it. 🤷‍♂️

If you want an easy calendar table, you can copy the one I created here https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

[MattAllington]

You need a calendar table and some DAX. The DAX logic is covered in this article

https://www.daxpatterns.com/semi-additive-calculations/