Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Joseph_Hchaime
Helper III
Helper III

Billing and Collection Report Problems

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

 

screenshot is an excel demo: 

DateBill NumberAmount BilledAmount Collected
01/01/2019100000500000
02/02/2019100000020000
01/03/20192000001000000
01/01/2020100000030000
01/01/20202000000100000

 

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? 

Joseph_Hchaime_0-1661948417448.png

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_0-1661948691119.png

 

 

4 REPLIES 4
Joseph_Hchaime
Helper III
Helper III

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
Super User
Super User

@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...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

there is a common bill number. 

 

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

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? 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.