How to make CALCULATE measure for last date exists before date in current row?

Question

I got this table:

	rounds_bot_date	values
1	2024-04-09	10
2	2024-04-10	20
0	2024-04-11	30
3	2024-04-15	40
4	2024-04-16	50
5	2024-04-17	60

How to make CALCULATE(SUM('table'[values]) with filter for last existing date before current date in row?

For example for 15.04 I want get value 30, for 10.04 - 10.

Answer 1

I solved problem this way. Cause I use data from Postgres, I use this query to add new column:

select  rounds_bot_date, 
        lead(rounds_bot_date) over(order by rounds_bot_date) as next_date,
        values
from    rounds r

So I get this table:

	rounds_bot_date	next_date	values
0	2024-04-09	2024-04-10	10
1	2024-04-10	2024-04-11	20
2	2024-04-11	2024-04-15	30
3	2024-04-15	2024-04-16	40
4	2024-04-16	2024-04-17	50
5	2024-04-17		60

I add new relationship between calendar_date and table[next_date].

My measure:

_2_2_values_prev_day = 
CALCULATE(
    SUM('table'[values]),
    USERELATIONSHIP('_calendar'[Date], 'values'[next_date])
)

Result (in Power BI it looks a bit different):

	rounds_bot_date	next_date	values_prev_date	values
0	2024-04-09	2024-04-10	nan	10
1	2024-04-10	2024-04-11	10	20
2	2024-04-11	2024-04-15	20	30
3	2024-04-15	2024-04-16	30	40
4	2024-04-16	2024-04-17	40	50
5	2024-04-17		50	60

In Power BI:

Maybe there's way to make it all with DAX or M coding and I just don't figure it out.

P. S. I can make same thing by adding 2 index columns (from 0 and 1). And make self merge table by this indexes. But in this case I think find prev date with SQL lead window function will works faster.