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 )
MFelix's avatar
MFelix
Icon for Super User rankSuper 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