Billing and Collection Report Problems

[Joseph_Hchaime]

Hi I am having trouble creating a table with the following fields:

screenshot is an excel demo: 

Date	Bill Number	Amount Billed	Amount Collected
01/01/2019	100000	50000	0
02/02/2019	100000	0	20000
01/03/2019	200000	100000	0
01/01/2020	100000	0	30000
01/01/2020	200000	0	100000

My data is hosted on an SQL server that's getting refreshed via API. So I direct query that data into powerbi and publish my reports on the service. 

The way the data is now is the following: 

1) Bills Table: Bill_ID, Bills_Issue_Date, Net Billed

2) Allocations Table (Payments): Bill_ID, Allocation_Date, Net Collected

I tried creating a calendar table and linking both above tables to it based on issue date and payment date 

in addition to that i tried linking both tables to each other by Bill_ID 

But it's not working. Can someone help me fix this relationship so I can make it work please? 

I can't link the dates table to only one date because I need the bill_issue date in addition to the date of any payments. 

Also One bill can have multiple payments or 0 payments (still awaiting payment). 

I want a result as follows: 

[Joseph_Hchaime]

Hi, I am still struggling with this. I cannot change the schema of the data. what can I do? 

the end result should show me for a certain payment the year of the bill issued and the year of collection 

[amitchandak]

@Joseph_Hchaime , You need to have a common Bill ID/Number in table two. These two tables should not join with each other

Once you have common bill and date tables; simple sum of columns from both table should do

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[Joseph_Hchaime]

there is a common bill number. 

ALL bill IDs in the allocations table are found in the bills table. But not necessarily vice versa.

[Joseph_Hchaime]

How am I supposed to get the allocation date then if I am going to do more time intelligence dax functions?

For instance. What if I want to know that a specific payment is divided between current year and previous years based on the date of payment? 

Can you please show me a relationship diagram of your solution?