How to use new functions (Window/Offset/Index) to retrieve previous row value based on partition

[CaveOfWonders]

Hi All,

I have been playing around with this but have failed to figure it out. Let's say we have a table like the one below:

ReportDate	Project	Project Phase	Actual Date
01-Dec-2022	a	1	04-Dec-2022
08-Dec-2022	a	1	11-Dec-2022
01-Dec-2022	b	4	02-May-2023
08-Dec-2022	b	4	03-May-2023

and I would like to calculate the previous [Actual Date] based on a partition of [ReportDate], [Project], and [Project Phase], so for example:

ReportDate	Project	Project Phase	Actual Date	Previous Actual Date
01-Dec-2022	a	1	04-Dec-2022
08-Dec-2022	a	1	11-Dec-2022	04-Dec-2022
01-Dec-2022	b	4	02-May-2023
08-Dec-2022	b	4	03-May-2023	02-May-2023

Please note there are multiple reporting periods, multiple projects and multiple phases in the dataset and this is just a subset for ease of undersatnding.

How could you do this in a calculated column and how would you do this in a measure and which function (Index/Offset/Window) would be best for this?

Thank you for your help on this.

[CaveOfWonders]

Hi, thank you for your reply. Unfortunately, this is not working on the actual table.

As you can see for project 191, phase 1, the [Actual Date] is '20/Nov/2022' for the [Reporting Date] '17-Nov-2022' , however, for the next reporting date (24-Nov-2022), it is showing 21-Nov-2022 and not the expected 20/Nov/2022.

and for the same project in phase 2 it is not working at all?

[Ahmedx]

and like this?

Столбец = 
 ПОИСКЗНАЧЕНИЕ (
     'таблица' [Фактическая дата],
     таблица [Фактическая дата],
         КОМПЕНСИРОВАТЬ (
             -1,
             ПОДВЕДЕМ ИТОГ (
                 ALLSELECTED (таблица),
                 таблица [Фактическая дата]
             ),
             ORDERBY (таблица [Фактическая дата], ASC)
         ),
     'таблица'[ Проект ], 'таблица'[Проект ],таблица[Этап проекта],[Этап проекта]
 )  

[CaveOfWonders]

Sorry, could you send a screenshot of the code as it's coming through in a different language.

[Ahmedx]

download the file

https://dropmefiles.com/OKn0X

[CaveOfWonders]

This is not working consistently:

[Ahmedx]

you can create a column and write like this

Столбец =
 ПОИСКЗНАЧЕНИЕ (
     'таблица' [Фактическая дата],
     таблица [Фактическая дата],
         КОМПЕНСИРОВАТЬ (
             -1,
             ПОДВЕДЕМ ИТОГ (
                 ALLSELECTED (таблица),
                 таблица [Фактическая дата]
             ),
             ORDERBY (таблица [Фактическая дата], ASC)
         ),
     'таблица'[ проект ], 'таблица'[проект ]
 )