Sometimes you want to have information from “the previous row” in your table. The problem is, your measures don’t have row context, so it doesn’t know on which row it is. Creating a column will solve this problem easily, but generally, it’s best practice to use measures. In this blog, I’ll explain what is needed to let a measure see the necessary row context for retrieving information from the previous row in a table visual.
Our table looks like this:
And what I want to know is the number of days between those two rows. Therefore I need to know what the previous date was. To do this, I create a measure and will explain step by step what and why we’re doing this:
Previous Date of Sales = MAXX( ADDCOLUMNS( SUMMARIZE( 'FactInternetSales' ,'Product Name'[EnglishProductName] ,'FactInternetSales'[OrderDate] ) ,"@PreviousDate" , CALCULATE( MAX('FactInternetSales'[OrderDate]) ,FILTER( ALL('FactInternetSales'[OrderDate]) ,'FactInternetSales'[OrderDate] < EARLIER('FactInternetSales'[OrderDate]) ) ) ) ,[@PreviousDate] )
First of all, using SUMMARIZE(), we create a table that’s grouped by the distinct values from the columns as can be seen in our table visual on the first image. This way we create the necessary row context that looks the same as the filter context in our table visual.
The next part is adding a column where we are going to add the previous order date. With Max, we want to get the latest value. In our filter statement, we request all the order dates (ignoring all filters), where the order date is before the order date in the current filter context.
Now the first, most important part, is done and it’s time to use our base measure in different kinds of calculations, for example, How many days were between the current sale and the previous one? Or: What was the value of the previous sale? We can now create very easy calculations using our Previous Date measure.
Value of Previous Date Sales = VAR PreviousDate = [Previous Date of Sales] RETURN CALCULATE( [Sum Amount] ,'FactInternetSales'[OrderDate] = PreviousDate )
Days since last sale = DATEDIFF( [Previous Date of Sales] , MIN(FactInternetSales[OrderDate]) , DAY )
Hopefully, this will help in your DAX adventures!