Power BI - How to get the previous 3 rows from any chosen row

Question

this might be a bit confusing but on a Power BI table, I was wondering how would I get the previous 3 rows from any row? Basically, what I am trying to do is to mark 'yes' on the 'Past 3 recent from current' column based from the current date we are in. So if 'Current' is populated with Y, is there a way to populate the 3 previous row (dates) 'yes' on the last column? Would I have to create a calculated column or a measure? Please let me know if this is confusing. The image is the final outcome that I am trying to achieve.

The three previous rows according to what? Power BI uses an algorithm to improve data compression and the order of the rows in the data model table may not be what you see if you create a visual that is sorted by date. Can you come up with a better logic than just the sort order? Is it the largest three date values that are smaller than the current date, maybe? But which date? — teylyn
Hi @teylyn According to the current date, where 'Current' is marked as 'Y'. Yes, It would be based off either the end date or start date. The table is already sorted in order like this. I mean I can try adding an index column? — dee
Please update your question with these details. Nobody wants to read through a wall of comments to find out what you're really after. Also, be more specific. Do you want to use start date or end date? You can't use both and "either" is not precise enough. — teylyn
What is - current date? Is that today? or selected date from slicer? — mkRabbani
@mkRabbani there no actual current date. I marked current as Y because the current date falls into the range between start and end date — dee

sergiom sergiom · Accepted Answer · 2021-01-27T15:40:33

If I understand your question, assuming that the T[Start Date] column has no duplicate values, like in your sample table, this calculated column could work. It first compute a variable CurrentDate with the T[Start Date] with T[Current] = "Y" and then it uses the TOPN function to get the top 3 T[Start Date] with less difference from the CurrentDate, using FILTER to remove dates greater or equal than CurrentDate

Past 3 recent from current =
VAR CurrentDate =
    CALCULATE (
        MAX ( T[Start Date] ),
        T[Current] = "Y",
        REMOVEFILTERS ( T )
    )
VAR PreviousStartDates =
    FILTER (
        VALUES ( T[Start Date] ),
        T[Start Date] < CurrentDate
    )
VAR Top3 =
    TOPN (
        3,
        PreviousStartDates,
        CurrentDate - T[Start Date], ASC
    )
VAR Result =
    IF (
        T[Start Date]
            IN Top3,
        "yes",
        "N"
    )
RETURN
    Result

The core of the code is the TOPN function, that retrieves the TOP 3 dates sorted by difference in days from the CurrentDate.

The first parameter is the number of rows to be returned, 3 in our case

The second parameter is the table to be sorted, that in our case is the variable PreviousStartDates, a table variable with a single column, T[Start Date], filtered to remove the dates greater or equal to CurrentDate

The third parameter is the expression to be used to sort the table specified as second parameter. In our case it's the CurrentDate - T[Start Date], the difference in days.

The fourth parameter, ASC, specifies the sorting order to go from the lowest days difference to the greatest.

Power BI - How to get the previous 3 rows from any chosen row

1 Answers