How to calculate Average Customer volume and Business interaction Period

[derekli1700]

Date of Action	Customer	Volume	Transaction	Assets
13/01/2020	Customer A	44,499.2	1,148	60
5/03/2020	Customer B	2,186.1	16	1
5/06/2021	Customer A	145,243.4	508	106
26/08/2021	Customer B	631.6	16	4
5/04/2022	Customer C	1,423.1	32	12
26/06/2022	Customer A	758.7	15	2
31/03/2023	Customer C	62,440.1	214	58
4/08/2023	Customer C	2,245.7	17	1
15/08/2023	Customer B	220,562.9	854	63
28/11/2023	Customer A	1,669.1	29	8
6/03/2024	Customer C	42,202.8	58	12
23/05/2024	Customer B	26,053.9	103	29
25/07/2024	Customer A	15,330.9	287	51

This is a sample simplified dataset of the datasheet i'm using for my PowerBI dashboard.

I wish to create 3 visuals:
1) One card showing the overall average period of time for customer activity before ceasing

2) One card showing the average customer volume

3) One visual (any type) that best shows the average increase/decrease of volume for 2020 - 2024 respectively. eg) 2020: 4% +. 2021: -2% etc etc

Thanks!

[Anonymous]

Thanks for the reply from rajendraongole1 , please allow me to provide another insight: 

Hi  @derekli1700 ,

You can use the month and year of the datediff() function if you want to reflect it as a year or month:

Month:

Create calculated column:

ActivityDurationmonth = DATEDIFF([FirstActionDate], [LastActionDate], MONTH)

Create measure:

FirstActionDatemonth =
AVERAGEX(
    ALL('CVolumne'),[ActivityDurationmonth])

Year:

Create calculated column:

ActivityDurationyear = DATEDIFF([FirstActionDate], [LastActionDate], YEAR)

Create measure:

FirstActionDateyear =
AVERAGEX(
    ALL('CVolumne'),[ActivityDurationyear])

Result:

The problem of inconsistent results between Excel and power bi, I think it should be caused by the way Excel and power bi deal with rounding, the specific explanation of this content you can check the following link.

Excel vs Power Query: The Rounding Dilemma - Microsoft Fabric Community

Solved: Different Results Power BI Desktop vs Excel - Microsoft Fabric Community

Best Regards,

Liu Yang

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

[derekli1700]

Hey - i was previously using this formula to calculate the average duration and the difference between this measure and this one you posted was a one year difference - thoughts?

Measure= AVERAGEX(SUMMARIZE(VALUES('CVolumne'[Customer]),'CVolumne'[Customer],"A",MAX('CVolumne'[Date of Action]),"B",MIN('CVolumne'[Date of Action])),DATEDIFF([b],[a],MONTH))

[rajendraongole1]

Hi @derekli1700 - create a calculated column as below 

FirstActionDate =

CALCULATE(

    MIN('CVolumne'[Date of Action]),

    ALLEXCEPT('CVolumne', 'CVolumne'[Customer])

)

Last action date

LastActionDate =

CALCULATE(

    MAX('Cvolumne'[Date of Action]),

    ALLEXCEPT('Cvolumne', 'Cvolumne'[Customer])

)

Difference between first and last action dates

ActivityDuration = DATEDIFF([FirstActionDate], [LastActionDate], DAY)

another calculated column for year

Year = YEAR('CVolumne'[Date of Action])

First card visual ,

AvgActivityPeriod = AVERAGE('Cvolumne'[ActivityDuration])

Second visual

AvgCustomerVolume = AVERAGE('Cvolumne'[Volume])

yoy percentage calculation as below: I have created a date table and linked between date table to cvolumne Date of action to Date column.

YoYChange =

VAR CurrentYearVolume = [TotalVolumeByYear]

VAR PreviousYearVolume =

    CALCULATE(

        [TotalVolumeByYear],

        PREVIOUSYEAR('DateTable'[Date])

    )

RETURN

IF(

    ISBLANK(PreviousYearVolume),

    0,  -- Or you could return BLANK() if you prefer.

    DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, 0)

)

Hope it helps

[derekli1700]

Hi, im up to 2nd visual card instructions right now and i have some questions:
1. For the 1st card visual - i noticed the card result is 1.33k. Is there a way to get this to be reflected in years or months instead?

2. For the 2nd visual: I manually calculated the excel sheet average to be 43.65k rather than 43.48k as seen in the power bi visual. Is there usually a reason for this discrepancy? Moreover - is there a way to get the monthly volume average as well?

Thank you!