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

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      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.