New Measure based on the some previous row value of the same measure

[Esra90]

Hello,

I have a table consisting of Date column and MeasuredDate measured column.

I am trying to add another measured column called isValid that is set to 1 for the very first date of table and set to either 0 or 1 for the other dates of the table according to a comparison. You could see what I am trying to have on the left. Also I tried to show detailed steps on the right table. The logic behind setting isValid column value is as follows:

1. The isValid column of the row with the earliest date will be set to 1 without using any comparison.
2. There will be applied an if check to set isValid for the other rows except the row with the earliest date.
3. Comparison date is the main variable of the if check.
4. ComparisonDate is the MeasuredDate of the last Valid (isValid 1) row. So as the first row is the the last Valid row for the second row, the comparison date of the second row will be set to MeasuredDate of the first row.
5. If ComparisonDate is Blank or Date is less than or equal to ComparisonDate then isValid will be set to 0, otherwise when Date is greater than ComparisonDate, then isValid will be set to 1.
6. As the Date value of the second row is not greater than ComparisonDate, isValid for the second row will be set to 0.
7. The first row is still the last Valid row for the 3rd row. So ComparisonDate for the 3rd row will be set to MeasuredDate of the first row.
8. As the 3rd row's Date value is greater than ComparisonDate, isValid value will be set to 1 for the 3rd row. Now the 3rd row is new last Valid row and its MeasuredDate will be used as ComparisonDate for the rows coming after the 3rd row until one of the rows is set to 1.
9. 9th row is the last Valid row and its MeasuredDate(Blank) will be used in the following rows.
10. As 10th row's ComparisonDate is Blank isValid will be set to 0.

Many thanks.

[Anonymous]

Hi @Esra90,

In fact, current dax expression does not support recursion calculations. Current you can only use them to achieve the cumulative or rolling calculations based on iterator function and filters.

Previous Value (“Recursion”) in DAX – Greg Deckler

Regards,

Xiaoxin Sheng

[Esra90]

Hi, Thanks for the reply. Is there a way to keep or reach the measuredDate of the last valid (isValid 1) row in each iteration. For instance, let's assume we are in the 5th iteration with Date 01.03.2020 trying to calculate if it is valid. To do this we need to reach up to last valid which is the row with Date 28.02.2020 and measuredDate 02.03.2020 and get its measuredDate (02.03.2020) to compare with the date in the 5th row. How can we reach up to the last valid row with looping approach rather than using infinitely many if checks in each iteration? 

[Anonymous]

HI @Esra90,

Have you familiar with python script? If that is the case, you can consider using python to handle the recursion calculations at query table side.

Use Python in Power BI Power Query Editor - Power BI | Microsoft Learn

Python Function Recursion (w3schools.com)
Regards,

Xiaoxin Sheng