Getting the sum of the previous datetime value in report

[MartinVollaard]

Hi All,

What I need is a column with the numbers per date (that I have). In column 2 I'm looking for the value of the previous datetime. See column "Needed".

 

For previous I use now:

CALCULATE(SUM(OutputSQL[OMZET]);FILTER(ALL(OutputSQL[PEILDATUM]); OutputSQL[PEILDATUM] < MAX(OutputSQL[PEILDATUM])))

I know I add up the numbers less than the date but I don't know how to use only the previous date. I've tried EARLIER but got stuck.

Any directions?

[v-chuncz-msft · Accepted solution]

@MartinVollaard,

You may add an index column in Query Editor, then use DAX below to add a calculated column.

Column =
LOOKUPVALUE ( OutputSQL[OMZET], OutputSQL[Index], OutputSQL[Index] - 1 )

[MartinVollaard]

Thanks, Indexing did the trick.

[MartinVollaard]

I’m sorry but forgot to mention that my datetime column contains all the mentioned values. They are nog unique. In Total there are more than 4000 records.