Dax measure to dynamically Calculate the Gross Total

[user_dil]

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!

[Jihwan_Kim · Accepted 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:]
)