I am currently trying to create a report that shows how customers behave over time, but instead of doing this by date, I am doing it by customer age (number of months since they first became a customer). So using a date field isn't really an option, considering one customer may have started in Dec 2016 and another starts in Jun 2017.
What I'm trying to find is the month-over-month change in units purchased. If I was using a date field, I know that I could use
[Previous Month Total] = CALCULATE(SUM([Total Units]), PREVIOUSMONTH([FiscalDate]))
I also thought about using EARLIER() to find out but I don't think it would work in this case, as it requires row context that I'm not sure I could create. Below is a simplified version of the table that I'll be using.
ID Date Age Units 219 6/1/2017 0 10 219 7/1/2017 1 5 219 8/1/2017 2 4 219 9/1/2017 3 12 342 12/1/2016 0 500 342 1/1/2017 1 280 342 2/1/2017 2 325 342 3/1/2017 3 200 342 4/1/2017 4 250 342 5/1/2017 5 255 1 Answer
How about something like this?
PrevTotal = VAR CurrAge = SELECTEDVALUE(Table3[Age]) RETURN CALCULATE(SUM(Table3[Units]), ALL(Table3[Date]), Table3[Age] = CurrAge - 1) The CurrAge variable gives the Age evaluated in the current filter context. You then plug that into a filter in the CALCULATE line.
