Create table with estimated revenue

[vdburg]

Hi,

I want to estimate the revenue my customers should generate year by year from an initial date and till an end date with an agreed amount.
To make this reproducible I am using Dax.do modifying the meaning of some columns to my problem.
Let´s asumme I have these 2 Customers:

EVALUATE
VAR SAMPLE_=SUMMARIZECOLUMNS(
Customer[Customer Code],
Customer[Birth Date],
Customer[Date First Purchase],
Customer[Yearly Income],
filter(Customer,Customer[Customer Code] in {"11516","22337"}))
RETURN SAMPLE_

Birth Date = Initial Date
Date First Purchase = End Date
Yearly Income =Agreed Amount

I want to generate a table with all years from Initial Date till End Date, aggregating the annual Agreed Amount per year for all customers.
I should get a table like this with year and Revenue (the Customer Codes are for clarification):

	Customer Code
Year	22337	11516	Revenue
1953	20.000		20.000
1954	20.000	30.000	50.000
1955	20.000	30.000	50.000
1956	20.000	30.000	50.000
1957	20.000	30.000	50.000
1958	20.000	30.000	50.000
1959	20.000	30.000	50.000
1960	20.000	30.000	50.000
1961	20.000	30.000	50.000
1962	20.000	30.000	50.000
1963	20.000	30.000	50.000
1964	20.000	30.000	50.000
1965	20.000	30.000	50.000
1966	20.000	30.000	50.000
1967	20.000	30.000	50.000
1968	20.000	30.000	50.000
1969	20.000	30.000	50.000
1970	20.000	30.000	50.000
1971	20.000	30.000	50.000
1972	20.000	30.000	50.000
1973	20.000	30.000	50.000
1974	20.000	30.000	50.000
1975	20.000	30.000	50.000
1976	20.000	30.000	50.000
1977	20.000	30.000	50.000
1978	20.000	30.000	50.000
1979	20.000	30.000	50.000
1980	20.000	30.000	50.000
1981	20.000	30.000	50.000
1982	20.000	30.000	50.000
1983	20.000	30.000	50.000
1984	20.000	30.000	50.000
1985	20.000	30.000	50.000
1986	20.000	30.000	50.000
1987	20.000	30.000	50.000
1988	20.000	30.000	50.000
1989	20.000	30.000	50.000
1990	20.000	30.000	50.000
1991	20.000	30.000	50.000
1992	20.000	30.000	50.000
1993	20.000	30.000	50.000
1994	20.000	30.000	50.000
1995	20.000	30.000	50.000
1996	20.000	30.000	50.000
1997	20.000	30.000	50.000
1998	20.000	30.000	50.000
1999	20.000	30.000	50.000
2000	20.000	30.000	50.000
2001	20.000	30.000	50.000
2002	20.000	30.000	50.000
2003	20.000		20.000
2004	20.000		20.000

I have tried several approaches making the code too long, I am sure there is a way to generate this with a few lines of code.

Any suggestions?

Thanks

[vdburg]

Thanks @Fowmy ,

It´s exactly what I needed. Elegant solution.

Best regards,

Robert

[Fowmy · Accepted solution]

@vdburg 

Create the following table query and summarise as you need,

EVALUATE
SELECTCOLUMNS (
    GENERATE (
        SUMMARIZECOLUMNS (
            Customer[Customer Code],
            Customer[Birth Date],
            Customer[Date First Purchase],
            Customer[Yearly Income],
            FILTER ( Customer, Customer[Customer Code] IN { "11516", "22337" } )
        ),
        VAR _SYR =            YEAR ( Customer[Birth Date] )
        VAR _EYR =            YEAR ( Customer[Date First Purchase] )
        RETURN
            GENERATESERIES ( _SYR, _EYR )
    ),
    "CODE", Customer[Customer Code],
    "YEAR", [Value],
    "REVENUE", Customer[Yearly Income]
)