topic Re: Calculate date range when specific value is reached in DAX Commands and Tips

Calculate date range when specific value is reached

AntonV — Wed, 05 Apr 2023 16:32:18 GMT

Hi all,

I am struggling with a date diff calculation. I have found some similar cases in the community, but I can't seem te figure it out. I hope you can help me 😁

My dataset:

Table with companies
- Each company has a create date
Table with transactions from companies
- Each transaction is linked to a company
- Each transaction has a commission amount that belongs to the company
- Each transaction has a date

What I need to calculate:

I want to know how long it takes a company to gather €X (€75 & €50 & €30) in cumulative commission.

So I would need to calculate the difference between the company create date and the transaction date (of the transaction that puts the company above the €X bar).

And from all this, I would need to get the avg duration it takes a company to get to €X.

My questions:

How do I calculate the transaction date (of the transaction that puts the company above the €X bar)?
How do I calculate the date diff/duration between the create date & the transaction date?
How do I calculate the avg duration for all companies who have reached the €X bar?

You would make my week with a solution 🙃

Thanks in advance!!

Anton

Re: Calculate date range when specific value is reached

johnt75 — Thu, 06 Apr 2023 08:45:48 GMT

I think that for performance reasons you will be better creating a set of calculated columns, one for each commission target, as doing the whole thing just with measures will not perform well with even a reasonable number of transactions.

To start with, I would create a measure to calculate the cumulative commission earned up to a particular point in time, e.g.

Cumulative commission = VAR MaxTransactionDate = MAX ( 'Transactions'[Date] ) VAR Result = CALCULATE ( SUM ( 'Transactions'[Commission] ), REMOVEFILTERS ( 'Transactions'[Date] ), 'Transactions'[Date] <= MaxTransactionDate ) RETURN Result

And then I would create columns like

First reached 30 = FIRSTNONBLANK ( 'Transactions'[Date], IF ( [Cumulative commission] >= 30, 1 ) )

Now that you have the values for the first dates computed you could get the average time taken with

Avg time to 30 = AVERAGEX ( FILTER ( Companies, NOT ISBLANK ( Companies[First reached 30] ) ), DATEDIFF ( Companies[Created], Companies[First reached 30], DAY ) )

Re: Calculate date range when specific value is reached

AntonV — Thu, 06 Apr 2023 11:06:55 GMT

johnt75 Thanks for the respons!

I have created the first measure (Cumulative commission).

Now I tried to create the calculated collumn, but I get the following error:

How can I improve my computer memory? Or what can I do?

I have tried to calculate the collumn 3 times...

Re: Calculate date range when specific value is reached

johnt75 — Thu, 06 Apr 2023 11:16:18 GMT

You can try and create a summary table, like

Summary Table = ADDCOLUMNS ( SUMMARIZE ( 'Transactions', 'Transactions'[Company ID], 'Transactions'[Date] ), "@cumulative commission", VAR CurrentDate = 'Transactions'[Date] VAR Result = CALCULATE ( SUM ( 'Transactions'[Commission] ), REMOVEFILTERS ( 'Transactions'[Date] ), 'Transactions'[Date] <= CurrentDate ) RETURN Result )

This will calculate the cumulative commission for each company for each date which that company has transactions for. If that is successful then we can use that summary table to find the minimum date where the cumulative commission is above the threshold.

Re: Calculate date range when specific value is reached

AntonV — Thu, 06 Apr 2023 12:03:39 GMT

johnt75 I was able to generate the summary table, but how do I now calculate the date when a company reaches €30?

And how do I calculate the average duration that it takes to get there for all companies?

Thanks in advance. You are a life saver!!

Re: Calculate date range when specific value is reached

johnt75 — Thu, 06 Apr 2023 12:45:01 GMT

Create a one-to-many relationship from the company table to the summary table, then add a new calculated column to the company table,

Date reached 30 = CALCULATE ( MIN ( 'Summary Table'[Date] ), 'Summary Table'[@cumulative commission] >= 30 )

You can then create a measure like

Avg time to 30 = AVERAGEX ( FILTER ( Company, NOT ISBLANK ( Company[Date reached 30] ) ), DATEDIFF ( Company[Create date], Company[Date reached 30], DAY ) )

Re: Calculate date range when specific value is reached

AntonV — Thu, 06 Apr 2023 13:06:19 GMT

johnt75 Thanks a lot for the help. I found what I needed.

I do need to make one more calculation, but I am not sure if it's appropriate to keep asking you questions. If not, you can ingnore my message! A million thanks either way

What I now need to calculate with the same tables is: "How mutch commission is collected under the €30 bar for each year?"

Here you have some use cases:

Company 1:
- Y1: €50 commission
- Only €30 should be included in the calculation
Company 2:
1. Y1: €10 commission
2. Y2: €40 commission
3. Only €30 (€10 + €20) should be included in the calculation
Company 3:
1. Y1: €0 commission
2. Y2: €10 commission
3. Only €10 should be included in the calculation.

Based on this I need a total of all commissions earned under €30 per company per year. Hope my explenation is clear...

Again really appreciate the help and I hope you have time to help me a little further..

Re: Calculate date range when specific value is reached

johnt75 — Thu, 06 Apr 2023 13:37:31 GMT

Adapted from https://www.daxpatterns.com/semi-additive-calculations/. Link your date table to the summary table, then create the below measure

Commission under 30 = VAR MaxCommissionDates = ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Date'[Year] ), SUMMARIZE ( 'Summary Table', Company[Company ID] ) ), "@MaxCommissionDate", CALCULATE ( MAX ( 'Summary Table'[Date] ) ) ) VAR MaxCommissionDatesWithLineage = TREATAS ( MaxCommissionDates, 'Date'[Year], Company[ID], 'Date'[Date] ) VAR Result = CALCULATE ( SUMX ( 'Summary Table', IF ( 'Summary Table'[Commission] < 30, 'Summary Table'[Commission], 'Summary Table'[Commission] - 30 ) ), MaxCommissionDatesWithLineage ) RETURN Result

This should work out the last date for each company in each year, then iterate over those values and subtract 30 if the commission is above 30.