CODEWITHSUNDEEP
joinpowerbidaxvirtual-table
J Luo
J Luo

Reputation: 13

Can I 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:

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

sample PBI report

Opening balance

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

Expiry date

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

Desired output

Date ID A B C Total
01/11/2023 3000 7000 11000 21000
02/11/2023 3000 7000 11000 21000
03/11/2023 3000 7000 11000 21000
04/11/2023 3000 7000 11000 21000
05/11/2023 3000 7000 11000 21000
06/11/2023 3000 0 11000 14000
07/11/2023 3000 0 11000 14000
08/11/2023 3000 0 11000 14000
09/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

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

Upvotes: 1

Views: 96

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12061

Try:

Opening Balance =
  SUMX(
    DISTINCT(Dim_Product[Product Code]),
    (
      var sDate = MAX(Dim_Date[Date ID])
      var eDate = CALCULATE(MAX(Dim_Expiry_Date[Expiry Date]))
      var firstAvailableOpeningBalance = 
        TOPN( 1, 
          CALCULATETABLE(
            Fact_Account_Opening_Balance,
            REMOVEFILTERS(Dim_Date[Date ID]),
            Fact_Account_Opening_Balance[Date ID] <= sDate &&
            (ISBLANK(eDate) || sDate <= eDate)
          ),
          [Date ID], DESC
        )
      var result = SUMX(firstAvailableOpeningBalance, Fact_Account_Opening_Balance[Amount])
      return COALESCE(result, 0)
    )
  )

Notes:

  • SUMX(DISTINCT(Dim_Product[Product Code]), ... )
    Perform the calculation for each Product Code - this is needed for Total columns.
  • firstAvailableOpeningBalance
    Get's the first previous available balance available via TOPN.

Upvotes: 0

Related Questions