Find Total Average of Customer LifeTime Value

[madsbak]

Hi,

Okay.. Please, I need help for this one.

Background:

I am in the process of making a dashboard that show some KPIs in relation to the
customers life time value for my company.

So far I've found avg. customer value, avg. order frequency etc. This was no problem.

The Problem:

When I try to see the overall avg. Customer Lifespan Years Power BI teases me.
The dashboards works fine when a specific is selected, it shows the correct Avg. Customer lifeSpan Years.
However, when nothing is selected the KPI Card shows the max value between the first and last purchase date. I want the average. 

How I've calculated so far:

To find the customers first date of purchase:
Date of First Purchace = FIRSTDATE( 'DataSource - Fact'[Date] )

To find the customers latest date of purchase:
Date of Last Purchace = LASTDATE( 'DataSource - Fact'[Date] )

To find the number of days between:
Days Between = VALUE ( [Date of Last Purchace] - [Date of First Purchace] )

To find the Year:
Years = Divide ( Days Between, 365 )

As mentiod the number is correct for each customer in the column but the the single value in the KPI does not show the average of the customers:

This is only the top of the table. It goes from 1,92 years down to 0. But the KPI takes the total between First Data and Last Date.

The overall average should rather be something like 0,8 - 0,9.

How do I find the average Customer LifeSpan Years of all the customers together?

Kind regards

[amitchandak · Accepted solution]

@madsbak , Try a measure like

averageX(Values('DataSource - Fact'[customer]) , calculate(datediff ( Min( 'DataSource - Fact'[Date] ), max( 'DataSource - Fact'[Date] ) day)+1))

Pushing grouping of customer

[madsbak]

Thank you so much. It worked!