Forum Discussion
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 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
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 - 1Regards,
Pat
2 Replies
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 - 1Regards,
Pat
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
