Forum Discussion

Helper I

7 years ago

Solved

Previous day value

Hi,

I am trying to pull up the "DataPointValue" for the privious day (hoping to use this in a calculation).

Below is the snapshot of the data:

Current Formula used:

Previous row = CALCULATE

(MAX(Table[DataPointValue]),

TOPN(1, FILTER(ALLSELECTED(Table),

Table[AsOfDate] < MAX(Table[AsOfDate])),

Table[AsOfDate],

DESC

)

)

Problem:

When I try to select multiple "InstrumentDescription", the privous value is not getting calculated accurately. I need the calculation to dynamically group the data based on "InstrumentDescription" selection and pull up the privious value.

Also, is there a way to make the 1st record value for "Previous value" as 0?

I would greatly appreceate the help in solving this issue.

Thanks in advance.

Anonymous
7 years ago
sv11,

Is DataPointValue as existing field in your table? Please check my PBIX file.

Regards,
Lydia
previousrow.pbix119 KB

6 Replies

Greg_Deckler
Community Champion
7 years ago
I believe you want EARLIER like this:

Previous Row = VAR __previousDate = MAXX(FILTER(ALL('Table'),[AsOfDate] < EARLIER([AsOfDate])),[AsOfDate]) VAR __previous = MAXX(FILTER(ALL('Table'),[AsOfDate] = __previousDate),[DataPointValue]) RETURN __previous

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
- sv11
  Helper I
  7 years ago
  Greg_Deckler,
  
  Thank you for yor response. I have tried cleating a column based on your suggested script. The values do not seem to be matching.
  There are multiple "InstrumentDescription"'s for each date and I guess the calculation is is not grouping the data for each "InstrumentDescription" and pulling in the respective previous value.
  
  What do you suggest?
  Thanks a ton for your help.
  - Anonymous
    Not applicable
    7 years ago
    sv11,
    
    Please change your DAX to the following:
    
    Previous Row = VAR __previousDate = MAXX(FILTER(ALL('Table'),[AsOfDate] < EARLIER([AsOfDate])&&'Table'[InstrumentDescription]=EARLIER('Table'[InstrumentDescription])),[AsOfDate]) VAR __previous = MAXX(FILTER(ALL('Table'),[AsOfDate] = __previousDate),[DataPointValue]) RETURN IF(ISBLANK(__previous),0,__previous)
    
    Regards,
    Lydia

Related content