Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jacob2102
Helper II
Helper II

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
v-deddai1-msft
Community Support
Community Support

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
v-deddai1-msft
Community Support
Community Support

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

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.

amitchandak
Super User
Super User

@jacob2102 , Try like

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

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

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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