none
Getting the last date's value

    Question

  • Hello,

    I need to be able to show the last date's value for all dates where a fact measure exists.

    For example, let's say I have the following fact table:

    Date                  Category  Value

    01/01/2000       A              1

    01/02/2000       A              3

    01/03/2000       B              2

    01/05/2000       C              7

    What I need is to have a calculated measure that shows the last entry while respecting the value of the category in the slicer. So if I have no category in the slicer, the pivot table looks like this:

    Date                  Value  Calculated Measure

    01/01/2000       1         7

    01/02/2000       3         7

    01/03/2000       2         7

    01/05/2000       7         7

    But, if I have a category A in the slicer, it should look like this:

    Date                  Value  Calculated Measure

    01/01/2000       1         3

    01/02/2000       3         3


    • Edited by kostaz Wednesday, February 26, 2014 10:17 PM
    Wednesday, February 26, 2014 10:16 PM

Answers

  • Assuming you only have a single table in your data model like your example, this measure should work for you with Category as a row or a slicer:

    =CALCULATE(
       LOOKUPVALUE(TableName[Value],
         TableName[Date],
         MAX(TableName[Date])
       ),
       ALLEXCEPT(
           TableName,
           TableName[Category]
       )
    )
    Replace TableName with the actual table name.

    • Edited by Mike DietterickEditor Thursday, February 27, 2014 1:50 AM Typo
    • Marked as answer by kostaz Thursday, February 27, 2014 2:38 AM
    Wednesday, February 26, 2014 11:08 PM
    Answerer

All replies

  • Assuming you only have a single table in your data model like your example, this measure should work for you with Category as a row or a slicer:

    =CALCULATE(
       LOOKUPVALUE(TableName[Value],
         TableName[Date],
         MAX(TableName[Date])
       ),
       ALLEXCEPT(
           TableName,
           TableName[Category]
       )
    )
    Replace TableName with the actual table name.

    • Edited by Mike DietterickEditor Thursday, February 27, 2014 1:50 AM Typo
    • Marked as answer by kostaz Thursday, February 27, 2014 2:38 AM
    Wednesday, February 26, 2014 11:08 PM
    Answerer
  • Nice, thanks!
    Thursday, February 27, 2014 2:38 AM