topic Re: Find the last non blank value in DAX Commands and Tips

Find the last non blank value

jame_moriaty — Mon, 19 Sep 2022 16:12:35 GMT

Hi All,

I have data like below. I use the function :

IP Land Leased Area = TOTALQTD( SUM('Industrial Park'[LAND_Leased_Land_Area]),'Calendar'[DATE])

IP Land Leased Area Last Value = LASTNONBLANKVALUE('Calendar'[DATE], 'Industrial Park'[IP Land Leased Area])

However, the function IP Land Leased Area Last Value can not calculate the total exactly. The Total = 34.573 is wrong, the Total exactly = 35.360 because the Quang Ninh Province is not calculate in the Total (the reason is the Quang Ninh Province doesnot have the value at 31-dec-2021)

Please help me how to fix the calculate the Total = 35.360. Thank you!

Re: Find the last non blank value

Greg_Deckler — Mon, 19 Sep 2022 16:15:08 GMT

jame_moriaty This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Re: Find the last non blank value

jame_moriaty — Mon, 19 Sep 2022 16:26:51 GMT

Dear Greg,

I don't know how to fix this error . Can you help me ?

Re: Find the last non blank value

Greg_Deckler — Mon, 19 Sep 2022 16:37:35 GMT

jame_moriaty Try:

IP Land Leased Area Last Value Total = VAR __Table = SUMMARIZE('Table',[City],"__Value",[IP Land Leased Area Last Value]) RETURN IF(HASONEVALUE('Table'[City]), [IP Land Leased Area Last Value], SUMX(__Table,[__Value]))

Re: Find the last non blank value

tamerj1 — Mon, 19 Sep 2022 17:21:50 GMT

Hi jame_moriaty
Please try

IP Land Leased Area Last Value = SUMX ( VALUES ( TableName[City] ), CALCULATE ( LASTNONBLANKVALUE ( 'Calendar'[DATE], 'Industrial Park'[IP Land Leased Area] ) ) )

Re: Find the last non blank value

jame_moriaty — Mon, 19 Sep 2022 22:53:46 GMT

Thank you tamerj1 so much !