DAX formula for referencing previous cells in the row

[henryvu93]

Hi guys,

I created a matrix (screenshot below is a sample):

Basically "1" & "0" here are for determining the status of the customer: If customer bought something in a month, I marked them as Active ("1"). For this purpose, I used the function:

if([MonthlyPrice]  > 0 , 1, 0)

Now, I want to create a new measure: if they are "Active" over the last 3 months (current month, previous month & the month before previous month), I mark them as "Returning" customers. I would like the table to calculate this new status every month for e.g. if we are looking at Dec 2015, the status be based on Oct-15, Nov-15 & Dec-15 and so on. However, I don't know if we can reference the cutomer's "Active" status for previous months. I just wonder if we can use DAX to do something like that?

Any help/insight will be really appreciated! Thank you!

Henry

[henryvu93 · Accepted solution]

I think I found the solution for this: I created a date column based on [Month] & [Year]; then I calculate the moving sum of "Active" status over the last 3 months for each row using the DATESINPERIOD() function. The matrix was just an end result. Thank you for very useful discussion on:

https://community.powerbi.com/t5/Desktop/Moving-Average/m-p/43041#M16519