Forum Discussion

AntonV's avatar
AntonV
Icon for Helper I rankHelper 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 c...
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 )
)
johnt75's avatar
johnt75
Icon for Super User rankSuper 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 )
)