Forum Discussion
Billing and Collection Report Problems
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:
4 Replies
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
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?
there is a common bill number.
ALL bill IDs in the allocations table are found in the bills table. But not necessarily vice versa.
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
