Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GeorgeVepkhvadz
Frequent Visitor

Resources Exceeded - Running Total on Datediff calculated column

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? 

1 ACCEPTED SOLUTION
GeorgeVepkhvadz
Frequent Visitor

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. 

View solution in original post

3 REPLIES 3
Ray_Minds
Resolver I
Resolver I

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
Frequent Visitor

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors