Previous day value

[sv11]

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.

[Greg_Deckler]

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/3395...

[sv11]

@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.

[v-yuezhe-msft]

@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

[sv11]

@v-yuezhe-msft,

Thank you for your response and insight on DAX.

I have made changes to DAX as suggested, but I am still having trouble in pulling in the correct Previous value. 

This did solve part of my requirement (Starting with 0). 

Please find the snapshot below:

Ideally, the highlighted values should be the same.

Here is the DAX code I used:

Previous row1 =

VAR _PreviousDate = MAXX(FILTER(ALL(Table), Table[AsOfDate] < EARLIER(Table[AsOfDate]) && Table[InstrumentDescription] = EARLIER(Table[InstrumentDescription])), Table[AsOfDate])

VAR _Previous = MAXX(FILTER(ALL(Table), Table[AsOfDate] = _PreviousDate), Table[DataPointValue])

RETURN

IF(ISBLANK(_Previous), 0, _Previous)

I really appreceate your help.

Thanks

[v-yuezhe-msft · Accepted solution]

@sv11,

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

Regards,
Lydia

[sv11]

Thanks Lydia. This really helps.