Find the last non blank value

[jame_moriaty]

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!

[tamerj1 · Accepted solution]

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

[jame_moriaty]

Thank you tamerj1 so much !

[Greg_Deckler]

@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

[jame_moriaty]

Dear Greg,

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

[Greg_Deckler]

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