Forum Discussion

macwhe's avatar
macwhe
Helper I
5 years ago
Solved

weighted average date

HI ALL,   how can I calculte in a measure the payment average date per invoiced amount. Per example the payment weighted average date for invoice 001 should be 07/10/2020. Thanks Client   Invoice...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    This measure expression gets your expected result in a table visual with your Client column.  Please check it with your real data.  Replace "weighted" with your actual table name.

     

    Weighted Date =
    VAR mindate =
        MIN ( Weighted[Payment date] )
    VAR summary =
        ADDCOLUMNS (
            SUMMARIZE (
                Weighted,
                Weighted[Payment date],
                Weighted[Amount]
            ),
            "@days",
                DATEDIFF (
                    mindate,
                    Weighted[Payment date],
                    DAY
                )
        )
    VAR wavg =
        ROUND (
            SUMX (
                summary,
                Weighted[Amount] * [@days]
            )
                / SUMX (
                    summary,
                    Weighted[Amount]
                ),
            0
        )
    RETURN
        mindate + wavg - 1

     

    Regards,

    Pat

AlB's avatar
AlB
Community Champion
5 years ago

Hi macwhe 

Try this:

1. Place Client and Invoice in a table visual

2. Create this measure and place it in the visual

Measure =
CONVERT (
    INT (
        DIVIDE (
            SUMX ( Table1, Table1[Amount] * Table1[Payment date] ),
            SUM ( Table1[Amount] )
        )
    ),
    DATETIME
)

You might want to set the type to Date only to avoid showing the time

3. See it all at work in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers