locked
Display value at the end of timeline range selection RRS feed

  • Question

  • I need to design Power View Report for which Date will act as "Timeline", so when I select DAte as "2010-10-01" to "2010-11-01" then It should show me report having data on "2010-11-01 because it is latest data value that I am having.
    Wednesday, February 26, 2014 5:20 AM

Answers

  • Hi Sandip,

    You could give the following solution a try...

    You will need to add a Date table to your model if you don't have this already. At a minimum this table will have a single date column with all the dates in each year that you have data for in the other table(s) in your model. It's also a best practice to mark this as a date table.

    'Date' table

    Date
    01/01/2010
    02/01/2010
    03/01/2010
    04/01/2010
    05/01/2010
    06/01/2010
    07/01/2010
    08/01/2010
    09/01/2010
    10/01/2010
    11/01/2010
    12/01/2010
    13/01/2010
    14/01/2010
    15/01/2010
    16/01/2010
    17/01/2010
    18/01/2010
    19/01/2010
    20/01/2010
    21/01/2010
    22/01/2010
    23/01/2010
    24/01/2010
    25/01/2010
    26/01/2010
    27/01/2010
    28/01/2010
    29/01/2010
    30/01/2010

    etc...

    You could then define a measure that does a basic SUM aggregation for each of the columns that you require a measure for; these are your base measures. In this example, I am only focusing on the Positive column...

    SumOfPositive:=CALCULATE(SUM(Data[Positive]))


    Next, you can define another measure that provides the behaviour that you want, however there are two options for this depending on the exact behaviour required. They look very similar but they are different:

    PositiveLASTDATE:=
      CALCULATE(
        [SumOfPositive], 
        LASTDATE('Date'[Date])
      )

    OR

    PositiveLASTNONBLANK:=
      CALCULATE(
        [SumOfPositive], 
        LASTNONBLANK(
          'Date'[Date], 
          [SumOfPositive]
        )
      )

    The LASTDATE version of the measure will find the last date that you have filter in your range and return the value regardless of whether there is data for the date or not. This means that if you select "2010-10-01" to "2010-11-01" it will return the result for "2010-11-01" even if there is no data for this date. On the other hand, the LASTNONBLANK version of the measure will find the last date with data. This means that if you select "2010-10-01" to "2010-11-01" in this instance, if "2010-10-01" is the last date with data then the value for this date will be returned.


    undefined


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)





    • Proposed as answer by Michael Amadi Wednesday, February 26, 2014 8:56 AM
    • Edited by Michael Amadi Wednesday, February 26, 2014 9:56 AM Further clarifications
    • Marked as answer by BhaSandy Wednesday, February 26, 2014 12:08 PM
    Wednesday, February 26, 2014 8:01 AM
  • Hi Michael,

    This is correct way of doing it. thanks for the making it clear.

    • Marked as answer by BhaSandy Friday, February 28, 2014 9:42 AM
    Wednesday, February 26, 2014 12:09 PM

All replies

  • Hi Sandip,

    You could give the following solution a try...

    You will need to add a Date table to your model if you don't have this already. At a minimum this table will have a single date column with all the dates in each year that you have data for in the other table(s) in your model. It's also a best practice to mark this as a date table.

    'Date' table

    Date
    01/01/2010
    02/01/2010
    03/01/2010
    04/01/2010
    05/01/2010
    06/01/2010
    07/01/2010
    08/01/2010
    09/01/2010
    10/01/2010
    11/01/2010
    12/01/2010
    13/01/2010
    14/01/2010
    15/01/2010
    16/01/2010
    17/01/2010
    18/01/2010
    19/01/2010
    20/01/2010
    21/01/2010
    22/01/2010
    23/01/2010
    24/01/2010
    25/01/2010
    26/01/2010
    27/01/2010
    28/01/2010
    29/01/2010
    30/01/2010

    etc...

    You could then define a measure that does a basic SUM aggregation for each of the columns that you require a measure for; these are your base measures. In this example, I am only focusing on the Positive column...

    SumOfPositive:=CALCULATE(SUM(Data[Positive]))


    Next, you can define another measure that provides the behaviour that you want, however there are two options for this depending on the exact behaviour required. They look very similar but they are different:

    PositiveLASTDATE:=
      CALCULATE(
        [SumOfPositive], 
        LASTDATE('Date'[Date])
      )

    OR

    PositiveLASTNONBLANK:=
      CALCULATE(
        [SumOfPositive], 
        LASTNONBLANK(
          'Date'[Date], 
          [SumOfPositive]
        )
      )

    The LASTDATE version of the measure will find the last date that you have filter in your range and return the value regardless of whether there is data for the date or not. This means that if you select "2010-10-01" to "2010-11-01" it will return the result for "2010-11-01" even if there is no data for this date. On the other hand, the LASTNONBLANK version of the measure will find the last date with data. This means that if you select "2010-10-01" to "2010-11-01" in this instance, if "2010-10-01" is the last date with data then the value for this date will be returned.


    undefined


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)





    • Proposed as answer by Michael Amadi Wednesday, February 26, 2014 8:56 AM
    • Edited by Michael Amadi Wednesday, February 26, 2014 9:56 AM Further clarifications
    • Marked as answer by BhaSandy Wednesday, February 26, 2014 12:08 PM
    Wednesday, February 26, 2014 8:01 AM
  • Hi Michael,

    This is correct way of doing it. thanks for the making it clear.

    • Marked as answer by BhaSandy Friday, February 28, 2014 9:42 AM
    Wednesday, February 26, 2014 12:09 PM
  • Friday, February 28, 2014 9:42 AM