Check in the attached,
The main thing you are missing is to properly set the computing on your table calculations.
If you click on the table calculation pills little triangle, then click Edit Table Calculation, select Specific Dimensions, select both Dimensions and Restarting every CardNumber.
This way the date differences will compute only on dates from the same CardNumber.
Next I changed [Days between orders] for
if isnull([nextorderdate]) then 0 else DATEDIFF('day',ATTR([ChequeDate (DAY)]),[nextorderdate]) end
in order to get a 0 instead of a null on the first date order of a customer.
I compute the Avg with
WINDOW_SUM( [Days between orders] ) / ([Nb of orders] - 1 )
where Nb of orders is
WINDOW_COUNT( ATTR( [ChequeDate (DAY)] ) )
Pay attention, AvgDays is a nested table calculation, so you have to apply the computing on every Nested Calculations.
On Sheet234, I add those two measures values. I keep the same dimensions on the Rows but I hide the dates (right-click the pill , unselect Show Header).
And I add a filter in order to keep a single row per customer.
DD edmc.twbx 88.3 KB
Michel, thank you very much for such a detailed answer.