Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to 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.
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
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
93 | |
88 | |
70 |
User | Count |
---|---|
166 | |
133 | |
129 | |
102 | |
98 |