Calculate date range when specific value is reached

[AntonV]

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

[johnt75]

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.

[AntonV]

@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!!

[johnt75 · Accepted solution]

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 )
)

[AntonV]

@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:

1. Company 1:
   • Y1: €50 commission
   • Only €30 should be included in the calculation
2. Company 2:
   1. Y1: €10 commission
   2. Y2: €40 commission
   3. Only €30 (€10 + €20) should be included in the calculation
3. 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..

[johnt75]

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.

[AntonV]

@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...

[johnt75]

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 )
)