Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Show latest available value from a table

I currently have a data model that has the following data

 

Date                    Location                      Sales                Inventory

1/1/19                    A                                30                        40

2/1/19                    A                                 40                       50

3/1/19                    A                                  0                        60

 

This is similar to this link:  https://community.powerbi.com/t5/Desktop/Latest-Value/m-p/101331#M42482

 

However, with one caveat I can't get around.  I don't want the latest value but the latest filled in value.  So for Sales above I want to pull in a 40 and not 0.  So I would always want to pull in the latest value filled in the table.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    Something like this may be

     

    Measure =
MAXX (
    TOPN ( 1, FILTER ( Table1, Table1[Sales] <> 0 ), [Date], DESC ),
    [Sales]
)

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous so if 2/1/2019 was sales value 20 then you would pull 20 or 30? I guess latest value mean the value from latest date where sales > 0, correct?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Correct where the latest value is <> 0...  Although it's always going to be positive.

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

     

    Something like this may be

     

    Measure =
MAXX (
    TOPN ( 1, FILTER ( Table1, Table1[Sales] <> 0 ), [Date], DESC ),
    [Sales]
)