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

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
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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 )
)
      • AntonV's avatar
        AntonV
        Icon for Helper I rankHelper I
        3 years ago

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