cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Measure to visualize Avg amount through time

Hi all,

I'm encountering an issue with creating a measure based on two tables. Any help that can be provided will be highly appreciated. For the visualisation I want to create I'm using three tables:

• A calendar table
• An agreements table: showing per Contract ID the agreed upon amounts in a certain period (start and end date)
• An used table showing the amounts that were actually used for those for those contract ID's in a certain period

 Table agreements Contract ID Start date End date Amount 1 1-12-2019 30-6-2020 60 2 1-2-2020 15-6-2020 45

 Table Used Contract ID Start date End date Amount 1 1-12-2019 31-12-2019 5 1 1-1-2020 31-1-2020 3 1 1-2-2020 29-2-2020 7 1 1-3-2020 31-3-2020 9 1 1-4-2020 30-4-2020 4 1 1-5-2020 31-5-2020 3 1 1-6-2020 30-6-2020 8 2 1-2-2020 29-2-2020 6 2 1-3-2020 31-3-2020 9 2 1-4-2020 30-4-2020 12 2 1-5-2020 31-5-2020 8 2 1-6-2020 15-6-2020 3

The issue I'm having is to show the amounts in the agreements table through time. For example Contract ID 2 has a duration of 4,5 months (or 135 days), so in principle the agreement states that there are  (45/4.5)= 10 amounts per month. So either the duration of the contract in days can be divided by the amount times the days there are in that particular month is the equation I need to use or there is a better way to get this number that I'm not thinking of.

That amount can then be compared what is actually being used. So the ultimate result needs to look something like this:

 ID Agreed Used Agreed Used Agreed Used Agreed Used Agreed Used Agreed Used Agreed Used 1 8,5 5 8,5 3 8,5 7 8,5 9 8,5 4 8,5 3 8,5 8 2 10 6 10 9 10 12 10 8 5 3 dec-19 dec-19 jan-20 jan-20 feb-20 feb-20 mrt-20 mrt-20 apr-20 apr-20 mei-20 mei-20 jun-20 jun-20

The used part is easy as there is a relation between the calendar and the end date (as the end date of the used table will always fall within the month). But the agreed up on amounts I can't seem to make  work. I've tried the following but get errors as the start date and end date in both the Datediff and the filter function are not recognized:

Agreed per month = Calculate( Sum( 'Table agreements'[Amount]) / DateDiff( 'Table agreements'[Start date], 'Table agreements'[End date], Day) * Countrows('Calendar') ), Filter( 'Table agreements'[Start date] <= 'Calendar'[Date] &&  'Calendar'[Date] <= 'Table agreements'[Start date]) )

Note that at this point there is no relation between the Agreed table and the Calendar.

Only the Agreed table with the used table based on ID.

And the Used table with Calendar based on End date and Date respectively.

I really hope that someone can provide some insight, and like I said above, any help will be much appreciated.

Thanks, Maurice

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V

## Re: Measure to visualize Avg amount through time

Hi @Maussie94 ,

Try the following code:

``````Month Amount =
VAR start_date =
MAX ( Agreements[Start date] )
VAR end_date =
MAX ( Agreements[End date] )
VAR Total_Value =
CALCULATE (
SUM ( Agreements[Amount] ) / DATEDIFF ( start_date, end_date, DAY )
* DATEDIFF (
MAXX (
UNION ( ROW ( "date", start_date ), ROW ( "date", MIN ( 'Calendar'[Date] ) ) ),
[date]
),
MINX (
UNION ( ROW ( "date", end_date ), ROW ( "date", MAX ( 'Calendar'[Date] ) ) ),
[date]
),
DAY
)
) --, FILTER(Agreements, Agreements[Start date]<= MIN('Calendar'[Date]) && Agreements[End date]>=Max('Calendar'[Date])))
RETURN
IF ( Total_Value > 0, Total_Value )``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

3 REPLIES 3
Highlighted
Super User V

## Re: Measure to visualize Avg amount through time

Hi @Maussie94 ,

Try the following code:

``````Month Amount =
VAR start_date =
MAX ( Agreements[Start date] )
VAR end_date =
MAX ( Agreements[End date] )
VAR Total_Value =
CALCULATE (
SUM ( Agreements[Amount] ) / DATEDIFF ( start_date, end_date, DAY )
* DATEDIFF (
MAXX (
UNION ( ROW ( "date", start_date ), ROW ( "date", MIN ( 'Calendar'[Date] ) ) ),
[date]
),
MINX (
UNION ( ROW ( "date", end_date ), ROW ( "date", MAX ( 'Calendar'[Date] ) ) ),
[date]
),
DAY
)
) --, FILTER(Agreements, Agreements[Start date]<= MIN('Calendar'[Date]) && Agreements[End date]>=Max('Calendar'[Date])))
RETURN
IF ( Total_Value > 0, Total_Value )``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Community Support

## Re: Measure to visualize Avg amount through time

Hi @Maussie94 ,

Like this?

I don’t understand your calculation logic too well.

Could you tell how Agree=8.5 and Agree=10 are calculated?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Regular Visitor

## Re: Measure to visualize Avg amount through time

Hi @MFelix ,

Not sure how, but it indeed works now. The first time I tried it in a brand new file but received an error that the ID and the month names were not related. Tried it again in another file and now everything works. Still confused 😅, but very glad it works. Many thanks

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors