weighted average date

[macwhe]

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  Amount Payment date

ABC      001       1600      14/10/2020

ABC      001        400        12/09/2020

DDD     002         300        12/01/2020

DDD     002         350        12/03/2020

[AlB]

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 

[mahoneypat · Accepted solution]

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