Track balance tender quota qty by customers

[dothuyhang88]

Hi guys, I am new to Power BI and I want to seek for your help, how to track the balance qty of tender quota by cusomers with data source as below.

For example, the tender quota of SKU 1962 at Hospital A (tender valid from 01Jan2022 till 01Jan2023) is 1000

Total invoiced qty from 01Jan2022 till date is 40

=> Balance tender qty = 1000-40=960

1/ Total invoiced qty

Month	Invoice Date	Hospital (Account)_Name	SKU	Invoiced qty
JAN	7-Jan-22	A	1962	20.00
JAN	11-Jan-22	A	W8712	10.00
JAN	17-Jan-22	A	W8597	20.00
JAN	19-Jan-22	A	VCP359H	20.00
JAN	19-Jan-22	A	VCP317H	20.00
JAN	25-Jan-22	A	W9443	10.00
JAN	26-Jan-22	A	1962	10.00
JAN	19-Jan-22	A	B12LT	10.00
FEB	10-Feb-22	A	W9443	24.00
FEB	10-Feb-22	A	W9443	25.00
FEB	10-Feb-22	A	VCP359H	25.00
FEB	10-Feb-22	A	VCP359H	25.00
FEB	10-Feb-22	A	VCP359H	27.00
FEB	10-Feb-22	A	M650G	10.00
FEB	10-Feb-22	A	M650G	10.00
FEB	15-Feb-22	A	44150	12.00
FEB	25-Feb-22	A	1962	10.00
FEB	17-Feb-22	B	W9105	93.00
FEB	17-Feb-22	B	W9120	10.00
FEB	17-Feb-22	B	Z1032H	10.00
FEB	18-Feb-22	B	W31C	10.00
FEB	10-Feb-22	A	SR75	10.00
FEB	10-Feb-22	A	SR55	17.50
FEB	11-Feb-22	A	SR75	10.00
FEB	11-Feb-22	A	SR55	10.50
FEB	11-Feb-22	A	CDH29A	10.00

2/ Tender quota by hospitals with Tender date and Tender expiry date

Hospital (Account)_Name	Product	Tender valid	Tender expiry	Tender quota
A	1962	1-Jan-22	1-Jan-23	1000
A	W8712	1-Jan-22	1-Jan-23	1010
A	W8597	1-Jan-22	1-Jan-23	1020
A	VCP359H	1-Jan-22	1-Jan-23	1030
A	VCP317H	1-Jan-22	1-Jan-23	1040
A	W9443	1-Jan-22	1-Jan-23	1050
A	B12LT	1-Jan-22	1-Jan-23	1060
A	M650G	1-Jan-22	1-Jan-23	1070
A	44150	1-Jan-22	1-Jan-23	1080
B	W9105	1-Feb-22	1-Aug-22	1090
B	W9120	1-Feb-22	1-Aug-22	1100
B	Z1032H	1-Feb-22	1-Aug-22	1110
B	W31C	1-Feb-22	1-Aug-22	1120
A	SR75	1-Feb-22	1-Aug-22	1130
A	SR55	1-Feb-22	1-Aug-22	1140
A	CDH29A	1-Feb-22	1-Aug-22	1150

[dothuyhang88]

Hi guys, thank you a lot for your support.

I have uploaded the sample data source and expectation to Googledrive. Pls kindly assist.

https://drive.google.com/drive/folders/1mXVFOSN2Kncwz9-4BaGK8OCKGm9TD4SF?usp=sharing

Thanks

Hang

[v-cgao-msft · Accepted solution]

Hi @dothuyhang88 ，

First please create a relationship like this between the two tables.

Then please create a measure.

Balance tender qty = 
var _sum_of_Invoiced_qty = CALCULATE(
    SUM('Table 1'[Invoiced qty]),
        FILTER('Table 1',
        MAX('Table 1'[InvoiceDate])>=MAX('Table 2'[Tender valid])
        &&
        MAX('Table 1'[InvoiceDate])<=MAX('Table 2'[Tender expiry]))
    )
var _sum_of_Tender_quota = CALCULATE(SUM('Table 2'[Tender quota]))
return
_sum_of_Tender_quota-_sum_of_Invoiced_qty

Next drag these into the visual.

The result should be like this.

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_Gao

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