Forum Discussion

psorel's avatar
psorel
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate average each month after a visit date

NameSELLDATEVISIT DATE
Alan52024-01 
Alan102024-022024-02
Alan152024-03 
Alan202024-04 
Alan252024-05 
Alan302024-06 
Alan402024-07 
TED52024-01 
TED202024-02 
TED152024-032024-03
TED202024-04 
TED252024-05 
TED302024-06 
TED402024-07 

Hi,
I would like to create a table with employee names in row and column each month/year above the "Visit Date" and in values ​​the number of average sales month by month.

Exemple if i select 2024-05 in filter for alan, i see on the table 2024-03 /2024-04 / 2024-05 /2024-06 ( month after visit date) with the number of sells for each month and the average sell for each month too (15 for 2024-03), (15+20)/2 for 2024-04 and (15+20+25)/3 for 2024-05, etc...

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi psorel 

     

    Thanks for the reply from rajendraongole1.

     

    psorel , the following is for your reference.

     

    Create two measures as follow

     

    Measure = 
VAR _visit = CALCULATE(MAX([VISIT DATE]), ALLEXCEPT('Table', 'Table'[Name]))
RETURN
IF(MAX([DATE]) > _visit, 1, 0)

     

     

     

    average = 
VAR _earlier = MAX([DATE])
RETURN
AVERAGEX(FILTER(ALL('Table'), [Measure] = 1 && _earlier >= 'Table'[DATE] && 'Table'[Name] = MAX([Name])), [SELL])

     

     

    Output:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi psorel - create a cumulative sales starting from the "Visit Date.:

    Cumulative Sales =
    CALCULATE(
        SUM('Vistis'[SELL]),
        FILTER(
            ALL('Vistis'),
            'Vistis'[Name] = MAX('Vistis'[Name]) &&
            'Vistis'[DATE] >= MINX(FILTER(ALL('Vistis'), 'Vistis'[Name] = MAX('Vistis'[Name]) && 'Vistis'[VISIT DATE] <> BLANK()), 'Vistis'[VISIT DATE]) &&
            'Vistis'[DATE] <= MAX('Vistis'[DATE])
        )
    )

     

     
    This measure will calculate the number of months that have passed since the "Visit Date."
    Months Since Visit =
    DATEDIFF(
        MINX(FILTER(ALL('Vistis'), 'Vistis'[Name] = MAX('Vistis'[Name]) && 'Vistis'[VISIT DATE] <> BLANK()), 'Vistis'[VISIT DATE]),
        MAX('Vistis'[DATE]),
        MONTH
    ) + 1
     
    This measure calculates the average sales for each month, starting from the "Visit Date."
     
    Average Sales =
    DIVIDE([Cumulative Sales], [Months Since Visit])
     

     

     

    Hope it helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi psorel 

     

    Thanks for the reply from rajendraongole1.

     

    psorel , the following is for your reference.

     

    Create two measures as follow

     

    Measure = 
VAR _visit = CALCULATE(MAX([VISIT DATE]), ALLEXCEPT('Table', 'Table'[Name]))
RETURN
IF(MAX([DATE]) > _visit, 1, 0)

     

     

     

    average = 
VAR _earlier = MAX([DATE])
RETURN
AVERAGEX(FILTER(ALL('Table'), [Measure] = 1 && _earlier >= 'Table'[DATE] && 'Table'[Name] = MAX([Name])), [SELL])

     

     

    Output:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.