Resources Exceeded - Running Total on Datediff calculated column

[GeorgeVepkhvadz]

I am struggling with creating a measure which will do a running total based on calculated column which is datediff between two dates, Acquisiton date and transaction date.

I need a visual to show acquisition cohorts, and then how much Revenue they generated in M1, M2 etc..

Sounds very simple, but issue is that my Revenue table is on daily level, and has hundreds of milions of rows.

But, when I show Revenue in matrix visual, with acquisition monthly cohorts in Rows and Datediff in columns, it is quite quick.
However, I need to see it cumulated, and it has been driving me crazy, as I tried to optimeze measure for cumulative Revenue, multiple times, but issue is always that resources were exceeded.

What can I do? 

[Ray_Minds]

Step 1: create a calculated column to calculate the difference in months between the Acquisition Date and the Transaction Date:

DateDiffInMonths = DATEDIFF('Revenue'[AcquisitionDate], 'Revenue'[TransactionDate], MONTH)

step2:create a measure for the cumulative revenue. This measure will sum the revenue up to the current month for each cohort:
CumulativeRevenue =
CALCULATE(
SUM('Revenue'[Revenue]),
FILTER(
ALLSELECTED('Revenue'),
'Revenue'[AcquisitionDate] = MAX('Revenue'[AcquisitionDate]) &&
'Revenue'[DateDiffInMonths] <= MAX('Revenue'[DateDiffInMonths])
)
)

step3: To ensure the measure is optimized for performance, consider using variables and avoiding iterative functions where possible:

CumulativeRevenueOptimized =
VAR SelectedAcquisitionDate = MAX('Revenue'[AcquisitionDate])
VAR SelectedMonth = MAX('Revenue'[DateDiffInMonths])
RETURN
CALCULATE(
SUM('Revenue'[Revenue]),
FILTER(
ALLSELECTED('Revenue'),
'Revenue'[AcquisitionDate] = SelectedAcquisitionDate &&
'Revenue'[DateDiffInMonths] <= SelectedMonth
)
)

Step4: Create a Matrix Visual
1. Rows: Place the acquisition cohorts (based on the Acquisition Date) in the Rows.
2. Columns: Use the DateDiffInMonths column to show the month difference in the Columns.
3. Values: Add the CumulativeRevenueOptimized measure to the Values.

By creating the DateDiffInMonths column and using the optimized cumulative measure,
you should be able to generate a visual that shows acquisition cohorts and their cumulative revenue
over time without exceeding resource limits.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GeorgeVepkhvadz · Accepted solution]

I am using import mode, but after lot of struggle I finally managed to do it.

Way I did it, I created new dimension with only month numbers and made relationship between it and datediff, and then calculated running total using newly created dimension. 

[TomMartens]

Hey @GeorgeVepkhvadz ,

consider creating a pbix file that contains sample data (of course, not 100s of millions of rows) but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link). If you are importing the sample data from a spreadsheet isntead of using the manual input method share the spreadsheet as well.

Describe the expected result based on the sample data you provided.

Provide some of the measures you tried even if the did not work.

I'm also wondering if you are using direct query mode or import mode?

Regards,

Tom