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

Helper II

## Dax to fetch value from last row of a table

Hi friends

I have a table like below:

Date     Particulars    Debit    Credit    Balance

1/4/23         xxxxx           1000                  1000

5/4/23         xxxxx                         500        500

10/4/23       xxxxx           1500                  2000

10/4/23       xxxxx            1000                 3000

I want to fetch the value in "Balance" column from the last row i.e.3000.

I need to write a dax for this.  Pls help.

Thanks

1 ACCEPTED SOLUTION
Super User

Hi,

Add an Index column in Power Query, name it Index (starting from 1) and re-write the measure as:

``Last Balance = CALCULATE(SUM('Table'[Balance]),LASTNONBLANK('Table'[Index],MAX('Table'[Index])))``

If it solved your problem then please mark it as the solution so others can see it.

10 REPLIES 10
Helper II

Hi @MNedix

Yes, this is working.  Thanks a lot.

Super User

Hi,

Write this measure and drag it to a card visual

Bal = calculate([Balance],lastnonblank(Calendar[date],calculate([Balance])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Hi Asish

I tried this dax.  Since there are two rows with same date, it sums up both the balances.

I need the balance in last row only.

Super User

That raises yet another question.  If the date in the last 2 rows is the same, then why can't the answer be 2000?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

I used the following dax:

Calculate(sum(Table[balance]), lastnonblank(Table[date], calculate(sum(Table[balance])))

Since the context is date column and expression is sum, is it taking the sum of last 2 rows having the same date?

Helper II

Hi Asish

Can I get any solution? Thanks

Super User

You have not answered my previous question.  Furthermore MNedix has offered a solution to you.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Add an Index column in Power Query, name it Index (starting from 1) and re-write the measure as:

``Last Balance = CALCULATE(SUM('Table'[Balance]),LASTNONBLANK('Table'[Index],MAX('Table'[Index])))``

If it solved your problem then please mark it as the solution so others can see it.

Super User

Hi Thulasiram,

Could you please clarify what you mean by "last row"? Are you referring to the last row based on date?

Also, are you asking about the table visual or the data within the model table?

Helper II

Hi Gabry

Thanks for reply.  Yes, I mean the last row based on the date in ascending order.

I couldn't understand your second question.  I need a dax to bring that that last

row value.

Thanks a lot.

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.