Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Jl7988
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!

Product account balance PBI report 

 

Opening balance

Date IDAccount CodeProduct CodeAmount
1/11/20231001A1000
1/11/20231002A2000
1/11/20231001B3000
1/11/20231002B4000
1/11/20231001C5000
1/11/20231002C6000

 

Expiry date

Product CodeExpiry Date
A11/11/2023
B5/11/2023
C30/11/2023

 

Desired output

Date IDABCTotal
1/11/2023300070001100021000
2/11/2023300070001100021000
3/11/2023300070001100021000
4/11/2023300070001100021000
5/11/2023300070001100021000
6/11/2023300001100014000
7/11/2023300001100014000
8/11/2023300001100014000
9/11/2023300001100014000
10/11/2023300001100014000
11/11/2023300001100014000
12/11/2023001100011000
13/11/2023001100011000
14/11/2023001100011000
15/11/2023001100011000
16/11/2023001100011000
17/11/2023001100011000
18/11/2023001100011000
19/11/2023001100011000
20/11/2023001100011000
21/11/2023001100011000
22/11/2023001100011000
23/11/2023001100011000
24/11/2023001100011000
25/11/2023001100011000
26/11/2023001100011000
27/11/2023001100011000
28/11/2023001100011000
29/11/2023001100011000
30/11/2023001100011000

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please try something like below if it suits your requirement.

 

Jihwan_Kim_0-1702008664843.png

 

 

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.


Click here and Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Please try something like below if it suits your requirement.

 

Jihwan_Kim_0-1702008664843.png

 

 

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.


Click here and Go to My LinkedIn Page


@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 CodeExpiry Month
A202205
B202206
C202207

 

The sales by months for them are as follows:

Calendar monthProduct Code Amount
202204A1000
202205A1000
202206A1000
202207A1000
202208A1000
202204B1000
202205B1000
202206B1000
202207B1000
202208B1000
202204C1000
202205C1000
202206C1000
202207C1000
202208C1000

 

The desired output of accumulated sales is as follows:

Calendar monthABCTotal
2022041000100010003000
2022052000200020006000
202206 300030006000
202207  40004000

 

Accumulated sales PBI report 

 

Thank you very much! The measure works!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors