Create a virtual table in Dax to calculate account balance for products with different expiry dates

[Jl7988]

I have product A, B, C. They each have opening balance amount at 1/11/2023 ,say 3000, 7000 and 11000 for A,B,C respectively. They each have expiry dates, say A, B and C expires on 11/11/2023, 5/11/2023 and 30/11/2023 respectively. The opening balance needs to be calculated each month until the expiry dates for the product, i.e. from 1/11 to 5/11, the total opening balance will be 21000 (for all the 3 products) and from 6/11 to 11/11, the total opening balance will be 14000 (3000+11000 for A and C as B has expired) and from 12 to 30/11, the total opening balance will be 11000 (for C only) as both A and B have expired. Is there a way to create a single measure through creation of virtual table or others to achieve this outcome. I tried the following codes but it didn't work:

Calculated opening balance test= 
var latestdate=min(Dim_Date[Date ID])
var latestexpirydate=min(Dim_Expiry_Date[Expiry Date])
var datetable=filter(CROSSJOIN(all(Dim_Date),Dim_Expiry_Date),latestdate<=latestexpirydate)
return
sumx(datetable,sum(Fact_Account_Opening_Balance[Amount]))

Please find below the link to the sample PBI report. Appreciate some experts can help with the dax!! Thank you in advance!

Product account balance PBI report 

Opening balance

Date ID	Account Code	Product Code	Amount
1/11/2023	1001	A	1000
1/11/2023	1002	A	2000
1/11/2023	1001	B	3000
1/11/2023	1002	B	4000
1/11/2023	1001	C	5000
1/11/2023	1002	C	6000

Expiry date

Product Code	Expiry Date
A	11/11/2023
B	5/11/2023
C	30/11/2023

Desired output

Date ID	A	B	C	Total
1/11/2023	3000	7000	11000	21000
2/11/2023	3000	7000	11000	21000
3/11/2023	3000	7000	11000	21000
4/11/2023	3000	7000	11000	21000
5/11/2023	3000	7000	11000	21000
6/11/2023	3000	0	11000	14000
7/11/2023	3000	0	11000	14000
8/11/2023	3000	0	11000	14000
9/11/2023	3000	0	11000	14000
10/11/2023	3000	0	11000	14000
11/11/2023	3000	0	11000	14000
12/11/2023	0	0	11000	11000
13/11/2023	0	0	11000	11000
14/11/2023	0	0	11000	11000
15/11/2023	0	0	11000	11000
16/11/2023	0	0	11000	11000
17/11/2023	0	0	11000	11000
18/11/2023	0	0	11000	11000
19/11/2023	0	0	11000	11000
20/11/2023	0	0	11000	11000
21/11/2023	0	0	11000	11000
22/11/2023	0	0	11000	11000
23/11/2023	0	0	11000	11000
24/11/2023	0	0	11000	11000
25/11/2023	0	0	11000	11000
26/11/2023	0	0	11000	11000
27/11/2023	0	0	11000	11000
28/11/2023	0	0	11000	11000
29/11/2023	0	0	11000	11000
30/11/2023	0	0	11000	11000

[Jihwan_Kim · Accepted solution]

Hi,

Please try something like below if it suits your requirement.

Calculated opening balance test =
VAR _t =
    CALCULATETABLE (
        GENERATE (
            Fact_Account_Opening_Balance,
            FILTER (
                Dim_Date,
                Dim_Date[Date ID] <= RELATED ( Dim_Expiry_Date[Expiry Date] )
            )
        ),
        REMOVEFILTERS ( Dim_Date )
    )
RETURN
    SUMX (
        FILTER ( _t, Dim_Date[Date ID] = MAX ( Dim_Date[Date ID] ) ),
        Fact_Account_Opening_Balance[Amount]
    )

[Jl7988]

@Jihwan_Kim 

Thanks very much for your help. The measure works perfectly in regard to the scenario of account balance calculation.

However, I was trying to apply this measure to the calculation of accumulated sales . The target is to calculate accumulated sales till the month the product expires (and disregard any sales after the expiry month). When I applied this measure, it returned all sales of the products ending the expiry date in each month. Could you please advise how the measure should be adjusted to suit this scenario? Thanks very much! Pls follow the link below for the PBI report:

I have a list of products with the following expiry month:

Product Code	Expiry Month
A	202205
B	202206
C	202207

The sales by months for them are as follows:

Calendar month	Product Code	Amount
202204	A	1000
202205	A	1000
202206	A	1000
202207	A	1000
202208	A	1000
202204	B	1000
202205	B	1000
202206	B	1000
202207	B	1000
202208	B	1000
202204	C	1000
202205	C	1000
202206	C	1000
202207	C	1000
202208	C	1000

The desired output of accumulated sales is as follows:

Calendar month	A	B	C	Total
202204	1000	1000	1000	3000
202205	2000	2000	2000	6000
202206		3000	3000	6000
202207			4000	4000

Accumulated sales PBI report 

[Jl7988]

Thank you very much! The measure works!!