Forum Discussion

AntonV's avatar
AntonV
Helper I
3 years ago
Solved

Calculate date range when specific value is reached

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

date calculation
duration
  • johnt75's avatar
    johnt75
    3 years ago

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

7 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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 )
)
  • AntonV's avatar
    AntonV
    Helper I
    3 years ago

    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's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      AntonV
      Helper I
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

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