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

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

Reply
dothuyhang88
New Member

Track balance tender quota qty by customers

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 
SKUInvoiced qty
JAN7-Jan-22A1962       20.00
JAN11-Jan-22AW8712       10.00
JAN17-Jan-22AW8597       20.00
JAN19-Jan-22AVCP359H       20.00
JAN19-Jan-22AVCP317H       20.00
JAN25-Jan-22AW9443       10.00
JAN26-Jan-22A1962       10.00
JAN19-Jan-22AB12LT       10.00
FEB10-Feb-22AW9443       24.00
FEB10-Feb-22AW9443       25.00
FEB10-Feb-22AVCP359H       25.00
FEB10-Feb-22AVCP359H       25.00
FEB10-Feb-22AVCP359H       27.00
FEB10-Feb-22AM650G       10.00
FEB10-Feb-22AM650G       10.00
FEB15-Feb-22A44150       12.00
FEB25-Feb-22A1962       10.00
FEB17-Feb-22BW9105       93.00
FEB17-Feb-22BW9120       10.00
FEB17-Feb-22BZ1032H       10.00
FEB18-Feb-22BW31C       10.00
FEB10-Feb-22ASR75       10.00
FEB10-Feb-22ASR55       17.50
FEB11-Feb-22ASR75       10.00
FEB11-Feb-22ASR55       10.50
FEB11-Feb-22ACDH29A       10.00

 

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

 

Hospital
(Account)_Name
ProductTender validTender expiryTender quota
A19621-Jan-221-Jan-231000
AW87121-Jan-221-Jan-231010
AW85971-Jan-221-Jan-231020
AVCP359H1-Jan-221-Jan-231030
AVCP317H1-Jan-221-Jan-231040
AW94431-Jan-221-Jan-231050
AB12LT1-Jan-221-Jan-231060
AM650G1-Jan-221-Jan-231070
A441501-Jan-221-Jan-231080
BW91051-Feb-221-Aug-221090
BW91201-Feb-221-Aug-221100
BZ1032H1-Feb-221-Aug-221110
BW31C1-Feb-221-Aug-221120
ASR751-Feb-221-Aug-221130
ASR551-Feb-221-Aug-221140
ACDH29A1-Feb-221-Aug-221150
1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @dothuyhang88 ,

 

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

vcgaomsft_0-1647484675502.png

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.

vcgaomsft_1-1647484894469.png

The result should be like this.

vcgaomsft_2-1647484919917.png

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

View solution in original post

2 REPLIES 2
dothuyhang88
New Member

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
Community Support
Community Support

Hi @dothuyhang88 ,

 

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

vcgaomsft_0-1647484675502.png

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.

vcgaomsft_1-1647484894469.png

The result should be like this.

vcgaomsft_2-1647484919917.png

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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