Most Current cell value ignoring blanks

[thebigbamf]

I am trying to make a dashboard from an excel document that gets pulled in from our internal sharepoint that my staff keep up to date.  My dashboard should show the current total of workstations on the network.  I have thrown in different numbers to make sure the dashboard is working correctly, but it is pulling the max value instead of the most recent value, excluding blanks.

Table View:

I created a card under visualizations and the formula is:   

LatestTotalWorkstations = LASTNONBLANK(AD[Total Workstation Objects], 1)

This pulls the correct column (Total Workstation Objects) but defaults to the max value, 5000 in this case, of that column instead of the current total of 4800.  This forumula should always pull the most recent value.  How can I fix this? 

Thanks in advance!

[v-zhangti]

Hi, @thebigbamf 

You can try the following methods.

Measure = 
Var _maxdate=CALCULATE(MAX(AD[Date]),FILTER(ALL(AD),[Total Workstation Objects]<>BLANK()))
Return
CALCULATE(MAX(AD[Total Workstation Objects]),FILTER(ALL(AD),[Date]=_maxdate))

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[Ashish_Mathur]

Hi,

Create a Calendar Table and write this measure

Latest Total Workstations = CALCULATE(SUM(AD[Total Workstation Objects]),LASTNONBLANK('Calendar'[Date],CALCULATE(SUM(AD[Total Workstation Objects]))))

[amitchandak · Accepted solution]

@thebigbamf , Try a measure like, assuming [Total Workstation Objects] is a column

LatestTotalWorkstations = LASTNONBLANKValue(AD[Date], Sum(AD[Total Workstation Objects]) )

Power bi tutorial: DAX functions: lastnonblankvalue , firstnonblankvalue: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=26940s

[thebigbamf]

Thank you this worked perfectly!