Forum Discussion

Regular Visitor

3 years ago

Solved

Getting previous row value - Problem Blanks

Hello,

I am trying to retrive the previous value of a row, using the knowledge I read from other posts about this topic.

But, the data I get is incorrect. I dont understand why.

My table name is Data, and it has the following columns:

- I for Index. Running number (1,2,3,...)

- Number of sales

I want to calculate: Number of sales [I] - Number of sales [I-1], for each row

This is the dax I am using:

Sales Gr=

var Cur_Index = MAX(Data[I])

var Pre_Index = Cur_Index-1

var Pre_Value = CALCULATE( MAX(Data[Sales]),FILTER(Data, Data[I] = Pre_Index))

var Cur_Value = CALCULATE( MAX(Data[Sales]),FILTER(Data, Data[I] = Cur_Index))

Return

Cur_Value-Pre_Value

I Noticed that the Pre_Value returns blanks , while Cur_Value returnes correct values. This is the cause of the problem.

Why is that ?

Thanks for your help

Anonymous
3 years ago
Hi Justiy ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Sales Gr = VAR Cur_Index = MAX ( Data[data] ) VAR Pre_Index = Cur_Index - 1 VAR Pre_Value = CALCULATE ( MAX ( Data[Sales] ), FILTER ( ALL ( Data ), Data[data] = Pre_Index ) ) VAR Cur_Value = CALCULATE ( MAX ( Data[Sales] ), FILTER ( ALL ( Data ), Data[data] = Cur_Index ) ) RETURN Cur_Value - Pre_Value

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without priavcy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Getting previous row value.pbix24 KB

3 Replies

Anonymous
Not applicable
3 years ago
Hi Justiy ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Sales Gr = VAR Cur_Index = MAX ( Data[data] ) VAR Pre_Index = Cur_Index - 1 VAR Pre_Value = CALCULATE ( MAX ( Data[Sales] ), FILTER ( ALL ( Data ), Data[data] = Pre_Index ) ) VAR Cur_Value = CALCULATE ( MAX ( Data[Sales] ), FILTER ( ALL ( Data ), Data[data] = Cur_Index ) ) RETURN Cur_Value - Pre_Value

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without priavcy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Getting previous row value.pbix24 KB
- Justiy
  Regular Visitor
  3 years ago
  Yes it works. Thanks.
  My problem was that I needed to add to my filter - ALL() as you did.
Ashish_Mathur
Super User
3 years ago
Hi,

Share some data to work with. Do you have a Date column in your dataset? Do you want a calculated column or a mesure solution?

Related content