Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.


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.


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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors