cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Calculate between dynamic dates

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.

Super User

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors