Calculate between dynamic dates

[wabanek]

Hi All, 

I have been struglin with it for days. I try to calculate average of values beetwen two dates. The dates depence on selected date in slicer. So far I have below solution: 

sum_changes_monthly_payments = 
var selected_date = TODAY() - [Miara]
var end_date = CALCULATE(MIN(payment_monthly[helper end date]);payment_monthly[helper end date]>=selected_date)
var start_date = CALCULATE(MAX(payment_monthly[helper start date]);payment_monthly[helper start date]<=selected_date)

RETURN

CALCULATE(AVERAGE(payment_monthly[Value]);FILTER(payment_monthly;payment_monthly[helper start date] = start_date && payment_monthly[helper end date]= end_date))
/* & " : " & start_date & " : " & end_date & " : " & selected_date */

It calculate fine in some cases, for example I have below data: 

Month Date	Contract Number	Value	helper end date	helper start date
01.10.2018 00:00	284/AS	150	31.12.2030 00:00	26.06.2019 00:00
01.11.2018 00:00	284/AS	150	31.12.2030 00:00	26.06.2019 00:00
01.10.2018 00:00	284/AS	150	25.06.2019 00:00	18.09.2018 00:00
01.11.2018 00:00	284/AS	150	25.06.2019 00:00	18.09.2018 00:00

When I try to calculate with selected date before 26/06/2019 its seems to works fine, in case selected date is 26/06/2019 or later the calculation doesn't works. 

[technolog]

From what you've shared, it seems the issue arises when the selected date is 26/06/2019 or later. This might be because of the way you're determining the start_date and end_date based on the selected_date.

Let's break down the logic:

You're determining the selected_date as TODAY() - [Miara]. This means that the selected_date is dynamic based on the current date and the value of [Miara].

The end_date is the minimum helper end date that's greater than or equal to the selected_date.

The start_date is the maximum helper start date that's less than or equal to the selected_date.

Now, let's think about the scenario when the selected_date is 26/06/2019:

The end_date would be 31.12.2030 00:00 because it's the smallest helper end date that's greater than or equal to 26/06/2019.

The start_date would be 26.06.2019 00:00 because it's the largest helper start date that's less than or equal to 26/06/2019.

Given this, the filter in your RETURN statement would only consider rows where the helper start date is 26/06/2019 and the helper end date is 31.12.2030. This would exclude the rows with the helper start date of 18.09.2018.

To fix this, you might want to adjust the logic to consider all rows between the start_date and end_date, not just the ones that match them exactly.

Here's a suggestion:

Replace your RETURN statement with this:

RETURN
CALCULATE(
AVERAGE(payment_monthly[Value]),
FILTER(
payment_monthly,
payment_monthly[helper start date] <= selected_date && payment_monthly[helper end date] >= selected_date
)
)
This will consider all rows where the selected_date falls between the helper start date and helper end date. This should give you the correct average for any selected_date.