Cost Allocation over time.

[Anonymous]

Dearest Fellow PBI users,
I have a dataset of 23k leases. Each lease has its own start date, end date, category, and monthly amount. The start dates begin 1/1/2018 and end dates up to 12/31/30. I need to show cost by year, quarter, month, and category. I've created a date table. I cant get past this. I've followed a few examples including one from ExcelFont that massively expanded my data and inflated the numbers. Sample data included.
 When I do the calculations manually, I'm not able to group by date values as they are column headers. Please help me figure this one out. 

Lease #	Start Date	End Date	Monthly Amount	Category
1	8/1/2019	8/1/2024	$5.99	PC
1	7/1/2019	7/1/2024	$6	Server
1	3/1/2020	3/1/2025	$5.93	Phone
1	2/1/2020	2/1/2025	$5.96	Scanner
1	2/1/2018	2/1/2023	$5.94	Printer
6	11/1/2018	11/1/2023	$5.88	Data Ctr
7	7/1/2018	7/1/2023	$5.95	PC
8	3/1/2018	3/1/2023	$19.33	Server
9	9/1/2018	9/1/2023	$19.32	Phone
10	12/1/2018	12/1/2023	$12.43	Scanner
10	8/1/2018	8/1/2023	$25.15	Printer
10	5/1/2018	5/1/2023	$12.42	Data Ctr
10	6/1/2018	6/1/2023	$24.41	PC
10	4/1/2018	4/1/2023	$20.37	Server
15	5/1/2019	1/1/2025	$25.14	Phone
16	1/1/2020	9/1/2025	$48.89	Scanner
17	9/1/2020	12/1/2024	$49.46	Printer
18	12/1/2019	8/1/2026	$18.10	Data Ctr
19	8/1/2021	11/1/2024	$25.13	PC
20	11/1/2019	10/1/2025	$28.30	Server
21	10/1/2020	4/1/2025	$4.11	Phone
21	4/1/2020	10/1/2024	$8.94	Scanner
21	10/1/2019	12/1/2025	$28.18	Printer
21	12/1/2020	2/1/2026	$24.29	Data Ctr
25	2/1/2021	12/1/2027	$5.35	PC
26	12/1/2022	12/1/2026	$56.29	Server
27	9/1/2019	9/1/2024	$13.12	Phone
28	7/1/2020	7/1/2025	$11.36	Scanner
29	5/1/2020	5/1/2025	$27.57	Printer
30	2/1/2023	2/1/2027	$27.24	Data Ctr
31	3/1/2023	2/1/2028	$6.15	PC
32	8/1/2020	3/1/2027	$6.13	Server
33	9/1/2022	8/1/2025	$14.82	Phone
34	5/1/2021	9/1/2027	$8.05	Scanner
35	2/1/2022	5/1/2026	$6.07	Printer
36	6/1/2022	6/1/2027	$18.63	Data Ctr
37	11/1/2021	11/1/2026	$8.24	PC
38	3/1/2019	3/1/2024	$8.32	Server
39	3/1/2021	5/1/2024	$8.09	Phone
40	11/1/2020	3/1/2026	$5.98	Scanner
41	6/1/2019	11/1/2025	$49.40	Printer
42	2/10/2019	6/1/2024	$92.66	Data Ctr
43	2/11/2019	3/1/2028	$130.18	PC
44	2/12/2019	8/1/2024	$51.86	Server
45	2/13/2019	7/1/2024	$54.81	Phone
46	2/14/2019	3/1/2025	$16.85	Scanner
47	2/15/2019	2/1/2025	$24.48	Printer
48	2/16/2019	2/1/2023	$61.82	Data Ctr
49	2/17/2019	11/1/2023	$571.92	PC
49	2/18/2019	7/1/2023	$20.02	Server
49	2/19/2019	3/1/2023	$109.62	Phone
49	2/20/2019	9/1/2023	$119	Scanner
49	2/21/2019	12/1/2023	$1,292.80	Printer
54	2/22/2019	8/1/2023	$48.96	Data Ctr
55	2/23/2019	5/1/2023	$55.08	PC
56	2/24/2019	6/1/2023	$27.45	Server
57	2/25/2019	4/1/2023	$32.83	Phone
58	2/26/2019	1/1/2025	$88.90	Scanner
59	2/27/2019	9/1/2025	$23,215.72	Printer
60	2/28/2019	12/1/2024	$107.98	Data Ctr
60	3/1/2019	8/1/2026	$48.34	PC
60	3/2/2019	11/1/2024	$20.31	Server
60	3/3/2019	10/1/2025	$171.26	Phone
64	3/4/2019	8/1/2024	$3,009.88	Scanner
65	3/5/2019	7/1/2024	$58.36	Printer
66	3/6/2019	3/1/2025	$48.79	Data Ctr
67	3/7/2019	2/1/2025	$729.58	PC
68	3/8/2019	2/1/2023	$272.80	Server
69	3/9/2019	11/1/2023	$30.91	Phone

[Anonymous]

Hi @Anonymous ,

Please check the pbix file:

If I have misunderstood your needs, please feel free to contact us.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

After additonal evaluation, there is still an issue. If the in service date in in Q4, its putting the total amount in Q4 each year opposed to allocating it monthly. 
If a term is 60m, it should show cost for 60 consecutive months beginning at the start date and ending at the end date. 

[Anonymous]

I was able to replicate everything with one exception. I'm getting an error "end of expression was reached" I'm not able to see my error. Please take a look.

CostAllocation =
VAR _CurrentYear = MAX('Calendar'[Year])
VAR _CurrentQuarter = MAX('Calendar'[QuarterNum])
VAR _CurrentMonth = MAX('Calendar'[Month])
RETURN
CALCULATE(
[TotalLeaseCost],
FILTER(
'Lease',
YEAR('Lease'[IN_SERVICE_DATE]) <= _CurrentYear && YEAR('Lease'[FINAL_EOT_DATE]) >= _CurrentYear &&
QUARTER('Lease'[IN_SERVICE_DATE]) <= _CurrentQuarter && QUARTER('Lease'[FINAL_EOT_DATE]) >= _CurrentQuarter &&
MONTH('Lease'[IN_SERVICE_DATE]) <= _CurrentMonth && MONTH('Lease'[FINAL_EOT_DATE]]) >= _CurrentMonth
)
)

[Anonymous]

there was a double "]" on the final line, issue resolved!