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 tab...
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 )
v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity 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.