Dax to fetch value from last row of a table

[Thulasiram]

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

[Thulasiram]

Hi @MNedix 

Yes, this is working.  Thanks a lot.

[Ashish_Mathur]

Hi,

Write this measure and drag it to a card visual

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

Hope this helps.

[Thulasiram]

Hi Asish

Thanks for reply.

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.

[Ashish_Mathur]

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

[Thulasiram]

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?

[Thulasiram]

Hi Asish

Can I get any solution? Thanks

[Ashish_Mathur]

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

[MNedix · Accepted solution]

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.

[Gabry]

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?

[Thulasiram]

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.