cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Value of the last date that not is blank

Hi,

 

I have a data source similar to the example, with different indicators,  different dates and the value of the indicador for each date.

Sin título.png

I want to get the last value of the year, when the value is not 0.

If I use "calculate(lastdate([date]),not([value])=0)", I get "30/06/2020". But, when I try it with "lastnonblank" I get 43,50 for the Id 0.2 instead of 40,00.

 

The goal is show in a table the last value for the year (according to dates) when it is not 0, and If I filter for each quarter obtein the value for this date. In this case, I want to get for the year 2020 the value 79,80 for Id 0.1 and 40,00 for Id 0.2.

 

Could you help me to solve this dax expression?

 

Thanks,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Hi @jacob2102

 

LASTNONBLANK Returns the last value in the column, column, filtered by the current context, where the expression is not blank.  Because your date column is not arranged from smallest to largest, you get unexpected results. 

 

After I sorted the date column, I used the following measure to get the expected result:

 

 

Measure = LASTNONBLANKVALUE('Table'[Date],SUM('Table'[Value]))

 

 

 

Capture3.PNG     Capture4.PNG

 

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

 

Best Regards,

Dedmon Dai

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

@jacob2102 , Try like

LASTNONBLANKVALUE(Table[fecha], max(Table[valor]))

sumx(summarize(Table, Table[Id],"_1",LASTNONBLANKVALUE(Table[fecha], max(Table[valor]))),[_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi,

First of all, thanks for your answer, but this option is not the solution.

In this case I have all dates for the year, some of them with value and others with 0. If I use this dax expression I get 0 like a resoult, becasuse the last date is 31/12/2020, and at this moment I don't have value for this date.

 

In this case, I would like to obtain the value for the las date that not is 0, in the example 40,00 for ID 0.2 in the date 30/06/2020.

 

Thanks,

Microsoft
Microsoft

Hi @jacob2102

 

LASTNONBLANK Returns the last value in the column, column, filtered by the current context, where the expression is not blank.  Because your date column is not arranged from smallest to largest, you get unexpected results. 

 

After I sorted the date column, I used the following measure to get the expected result:

 

 

Measure = LASTNONBLANKVALUE('Table'[Date],SUM('Table'[Value]))

 

 

 

Capture3.PNG     Capture4.PNG

 

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

 

Best Regards,

Dedmon Dai

View solution in original post

Thanks for your answer.

 

One of the problemas was in the data order. I don't know why in although I orden the data in PowerQuery, this order not appear later in the table.

To solve the problem, I have ordered the dates, identify the values "0" like blanks and use your measure. With this changes, now I obtain the correct resoult.

 

Thanks.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors