cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

3 REPLIES 3
Highlighted
Super User V
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Community Support
Community Support

Re: Measure to visualize Avg amount through time

Hi @Maussie94 ,

 

Like this?

v-lionel-msft_0-1596788641619.png

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

Helpful resources

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