Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 IDStart dateEnd dateAmount
11-12-201930-6-202060
21-2-202015-6-202045

 

Table Used
Contract IDStart dateEnd dateAmount
11-12-201931-12-20195
11-1-202031-1-20203
11-2-202029-2-20207
11-3-202031-3-20209
11-4-202030-4-20204
11-5-202031-5-20203
11-6-202030-6-20208
21-2-202029-2-20206
21-3-202031-3-20209
21-4-202030-4-202012
21-5-202031-5-20208
21-6-202015-6-20203

 

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:

IDAgreedUsedAgreedUsedAgreedUsedAgreedUsedAgreedUsedAgreedUsedAgreedUsed
18,558,538,578,598,548,538,58
2    106109101210853
 dec-19dec-19jan-20jan-20feb-20feb-20mrt-20mrt-20apr-20apr-20mei-20mei-20jun-20jun-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

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    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 )

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    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 )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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.