Is it possible to sum the monthly results in a grand total for a formula using intersect?
I have this result from an intersect evaluated monthly:
but the grand total is not the sum of the months. I tried EVERYTHING! I can’t get the Sumx or AverageX to produce a sensible result.
while I understand these are iterating functions, I don’t understand why it won’t work if I use a virtual table:
‘!Dates (Core Table)’[Date].[Year],
‘!Dates (Core Table)’[Date].[Month],
VAR PrevUSERTBL = CALCULATETABLE(VALUES(‘D-Orders’[customerId]),DATEADD(’!Dates (Core Table)’[Date].[Date],-1,MONTH))
Var CurrentUserTBL =CALCULATETABLE(VALUES(‘D-Orders’[customerId]),FILTER(’!Dates (Core Table)’,’!Dates (Core Table)’[Date]<=MAX(’!Dates (Core Table)’[Date])))
Var PreviousMonthCrossReference = INTERSECT(CurrentUserTBL,PrevUSERTBL)
var CountRowPrime = COUNTROWS(PreviousMonthCrossReference)
//this is the formula that creates the result in the firt image
I used the intersect formula from the Var tables listed above to see how many customers return in the next month (consecutive month purchasers). In aggregate, I would like to determine what that amount is on average YTD. Or even the Sum YTD.
I was able to do this in excel easily but dax???