cancel
Showing results for
Did you mean:
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.

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

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]))``

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

4 REPLIES 4
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])

Proud to be a Super User!

Frequent Visitor

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

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]))``

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

Frequent Visitor

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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