Measure to visualize Avg amount through time

[Maussie94]

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

[v-lionel-msft]

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.

[MFelix · Accepted solution]

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 )

[Maussie94]

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