cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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!

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

1 ACCEPTED SOLUTION
Super User

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]
)
``````

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.

3 REPLIES 3
Super User

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]
)
``````

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.

Frequent Visitor

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

Frequent Visitor

Thank you very much! The measure works!!