The problem
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.
Usage
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!
Take care!
Side Quests 
Hello Erik. This is a great article and I found it insightful. Please could I have a mentorship session with you? I have a problem like this and am finding it so hard to resolve. I’m not sure if there is any thing am doing wrong. Your help will be of great importance. Thanks.
LikeLike
Hi Oluwatosin O Falola,
Thank you for the compliment! Unfortunately I don’t do mentorship sessions, but I would suggest you to go to the Power BI Community:
https://community.fabric.microsoft.com/t5/Microsoft-Power-BI-Community/ct-p/powerbi
Create an account and post your question there. It’s a great community of people who love to help!
LikeLike
Hi Erik – this code helped me tremendously in exactly what I needed. There seems to be a bug in it, and I can’t find where that is. I have data grouped by Subject number, and I highlight where there is a gap in the previous date to the current date. The code works perfectly, except for one instance where it thinks the previous date is a few rows before it. I created a table to just show the two first steps (summarize, and adding the column) which works perfectly. And then when I put those fields in a table, you can see where it goes wrong. You can see the last three records, where the date is june 4, June 5, June 5, June 5 (same date but diff time) they all have the previous date as June 3. For some reason it “broke” – at first I thought it was because of multiple records with the same date – but I have other subjects that have multiple dates that are working fine.
Previous Date =
MAXX(
ADDCOLUMNS(
SUMMARIZE(
‘MainInfo’
,’MainInfo'[SubjectID]
,’MainInfo'[EntryDate]
)
,”@PreviousDate”
, CALCULATE(
MAX(‘MainInfo'[EntryDate])
,FILTER(
ALL(‘MainInfo'[EntryDate])
,’MainInfo'[EntryDate] < EARLIER(‘MainInfo'[EntryDate])
)
)
)
,[@PreviousDate]
)
Site Subject Scan Date Previous Date
801 801001 4/26/2024 0:00 4/25/2024 0:00
801 801001 4/27/2024 0:00 4/26/2024 0:00
801 801001 4/28/2024 0:00 4/27/2024 0:00
801 801001 4/29/2024 0:00 4/28/2024 0:00
801 801001 4/30/2024 0:00 4/29/2024 0:00
801 801001 6/3/2024 12:06 4/30/2024 0:00
801 801001 6/3/2024 12:10 4/30/2024 0:00
801 801001 6/4/2024 12:11 6/3/2024 12:10
801 801001 6/5/2024 16:29 6/3/2024 12:10
801 801001 6/5/2024 16:31 6/3/2024 12:10
801 801001 6/5/2024 16:52 6/3/2024 12:10
Any help you can provide will be hugely appreciated – I’ve been working on this for quite some time and going around in circles. I can’t seem to figure out why sometimes the previous date measure is grabbing the wrong date and not the one right before it.
LikeLike