Skip to main content
cancel
Showing results for 
Search instead 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

Reply
matrin
New Member

Last column value

Hi dear community,

 

I am new on Power BI Desktop and I need some help!

 

I've got a metric named "number of people" that can't be cumulated.

 

I want to return the last value of the column "number of people".

 

In fact, I import a database that provide the details of the number of people every day of the month. However, I don't have the number of the month provided, as the format of the date is "month - year" and can't be changed for some reasons.

To be clear, the file is presented like this:

february; 23

february; 29

february; 19

Thus, I want to return the last value of the column (19) which is in fact the last value of the month.

 

Can you please help me with this?

 

Thanks a lot in advance!!!

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

If these are only Feb, then I think it is difficult. But if they are dates then you can do it easily.

There is very powerful function as part of Feb 2020 release that can help - lastnonblankvalue

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

lastnonblankvalue(Date,max(Table[Value])

View solution in original post

V-lianl-msft
Community Support
Community Support

Hi @matrin ,

 

If you want to get the last value of a column, you can try the following DAX.

Measure = CALCULATE(LASTNONBLANK('Table'[Column2],1),ALL('Table'))

If the problem persists,could you please share sample data or sample .pbix?

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

  1. Do you have data for multiple months?
  2. Is there no year column?
  3. Is there an index column to let the user know the order in which the same months should appear?

Share data for more months/year column/index column and shoe the expected result clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
V-lianl-msft
Community Support
Community Support

Hi @matrin ,

 

If you want to get the last value of a column, you can try the following DAX.

Measure = CALCULATE(LASTNONBLANK('Table'[Column2],1),ALL('Table'))

If the problem persists,could you please share sample data or sample .pbix?

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

If these are only Feb, then I think it is difficult. But if they are dates then you can do it easily.

There is very powerful function as part of Feb 2020 release that can help - lastnonblankvalue

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

lastnonblankvalue(Date,max(Table[Value])

Thanks for your help.

 

In fact, I don't want the maximum value but the very last one of the column.

 

You're probably right, it won't work without days...

 

Hi @matrin ,

 

With the formula I created, I can get the desired results in my test data.

If the problem persists,could you please share sample data or sample .pbix?

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors