Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Currently, I'm facing some trouble while trying to calculate the gross total in my Transactions Table. What I want is to calculate the gross total for each product based on the unit price. But when the unit price gets updated, only the invoices issued on or after the unit price is updated should have the updated unit price for gross total calculation and older records should remain with the older unit price.
For example;
Product code 113 unit price is updated on 3/06/2022, so for the gross total calculation unit price should be $2 for invoices issued before 3/06/2022 and $2.5 for invoices issued on or after 3/06/2022. This should dynamically change as well if the unit price is updated again.
Here are the two tables; Transactions and Product Pricing. Thanks in Advance!
Transactions
Invoice no | Invoice Date | Product code | Product Qty | Gross Total |
0012 | 2/06/2022 | 113 | 5 | |
0013 | 2/06/2022 | 114 | 3 | |
0014 | 2/06/2022 | 112 | 7 | |
0015 | 3/06/2022 | 112 | 12 | |
0016 | 3/06/2022 | 110 | 6 | |
0017 | 3/06/2022 | 115 | 3 | |
0018 | 4/06/2022 | 113 | 9 | |
0019 | 4/06/2022 | 115 | 15 | |
0020 | 4/06/2022 | 110 | 4 |
Product Pricing:
Product code | Unit Price ($) | Date updated | Active Flag |
110 | 3 | 4/04/2022 | 1 |
112 | 4 | 4/04/2022 | 1 |
113 | 2 | 5/05/2022 | 0 |
113 | 2.5 | 3/06/2022 | 1 |
114 | 3.5 | 2/04/2022 | 1 |
115 | 3 | 2/04/2022 | 1 |
Thanks in Advance!
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Qty measure: =
SUM( Transactions[Product Qty] )
Price per product measure: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _lastnonblankdateinpricetable =
MAXX (
FILTER ( 'Product Pricing', 'Product Pricing'[Date updated] <= _currentdate ),
'Product Pricing'[Date updated]
)
RETURN
IF (
[Qty measure:] <> BLANK (),
MAXX (
FILTER (
'Product Pricing',
'Product Pricing'[Date updated] = _lastnonblankdateinpricetable
),
'Product Pricing'[Unit Price ($)]
)
)
Gross total measure: =
SUMX (
SUMMARIZE (
Transactions,
'Product'[Product code],
'Calendar'[Date],
Transactions[Invoice no]
),
[Qty measure:] * [Price per product measure:]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Qty measure: =
SUM( Transactions[Product Qty] )
Price per product measure: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _lastnonblankdateinpricetable =
MAXX (
FILTER ( 'Product Pricing', 'Product Pricing'[Date updated] <= _currentdate ),
'Product Pricing'[Date updated]
)
RETURN
IF (
[Qty measure:] <> BLANK (),
MAXX (
FILTER (
'Product Pricing',
'Product Pricing'[Date updated] = _lastnonblankdateinpricetable
),
'Product Pricing'[Unit Price ($)]
)
)
Gross total measure: =
SUMX (
SUMMARIZE (
Transactions,
'Product'[Product code],
'Calendar'[Date],
Transactions[Invoice no]
),
[Qty measure:] * [Price per product measure:]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |