DAX Help: How to iterate a calculation over a single dataset based on a time period.

[HillUser1]

I am struggling to understand how I could perofrm this calculation within PowerBI/DAX.

Calculation: Create a weighted rating system to help our company rank customers. Our team has come up with a set of criteras to rate each cusotmer by. They have created a rating system to be based a time period of (1) year. If the customer has been with us for greater then (1) year then they want the calculation to be performed for each year then averaged.

Issue: I do not undestand how to perform the calculation when each customer does not exist for the same time period. How can I loop over a single table of customer table and dynmaically perform the calculcation and filter a single data set to perform the calculation. In my head it would require a For Each or While Statement, but so far in my DAX experince I have not seen a function like this.

Any help pointing me to other examples or a response saying this is outside DAX's fucntionaly would be helpfull. 

[HillUser1 · Accepted solution]

+ Update:

I will perform the required math outside of PowerBI (WinForm/VB.net) and write the calcuated values to a database.

[amitchandak]

@HillUser1 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[HillUser1]

I can not post a sample dataset becuase I dont know all the senstive fields and this PBIX combines multiple tables together. I created a sample table showing the calculation, but the issue is that sum for all the years or manually requires me to filter the table with a slicer.

Data Tables:

    Invoice Table (All invoices to sum total revenue)

    Work Order Table (All Quotes, Counts,

    Customer Table (Customer Name, ID, Address, Date Entered)

Sample Concept Table for Calculation