Forum Discussion

thebigbamf's avatar
thebigbamf
Frequent Visitor
2 years ago
Solved

Most Current cell value ignoring blanks

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!
Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

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

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    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.