Forum Discussion

GeorgWildmoser's avatar
GeorgWildmoser
Frequent Visitor
2 years ago
Solved

Dax Query Incorrect Total

Hi,

my DAX query is producing the correct values by line but the incorrect value for the total. 

 

I thought I could fix the total problem with sumx. But obviously I'm doing something wrong here.

 

The DAX query:

Netto (12M v.DocDate) =
// Define the maximum date in 'TimeIfDocumentElsePostingDate' table
var maxDate = MAX('TimeIfDocumentElsePostingDate'[Date Date])

// Get the current date minus one day
var aktDate = TODAY() - 1

// Determine which date to use: maxDate or the current date (aktDate)
var useDate = IF(maxDate <= aktDate, maxDate, aktDate)

// Create a table of dates within the past year from 'useDate'
var DateTbl = DATESINPERIOD('TimeIfDocumentElsePostingDate'[Date Date], useDate, -1, YEAR)

// Calculate the Intermediate List for the last 12 months (IL12M)
var IL12M =
    CALCULATETABLE(
        ADDCOLUMNS(
            // Summarize data from 'CUB_AccountsReceivable_Tax' table by certain dimensions and dates
            SUMMARIZE(
                CUB_AR,
                DIM_Dim5[Dimension5Display],
                DIM_VoucherAR[VoucherARBusinessKeySurrogate],
                TimeCloseDate[Date Date],
                TimeDueDate[Date Date]
            ),
            // Add a column for due date plus 90 days
            "@DueDatePlus90", DATEADD(TimeDueDate[Date Date], 90, DAY),
            // Add a column for the current close date, handling blanks and dates beyond 'useDate'
            "@CloseDateCur", IF(
                ISBLANK(TimeCloseDate[Date Date]) || TimeCloseDate[Date Date] > useDate,
                useDate,
                TimeCloseDate[Date Date]
            )
        ),
        DateTbl,
        // Filter where 'TimeDueDate'[TimeBusinessKeySurrogate] is not blank
        NOT ISBLANK('TimeDueDate'[TimeBusinessKeySurrogate]),
        // Filter by specific document types in 'DIM_VoucherAR'[VoucherARDocumentTypeShort]
        DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}
    )
// Summarize the filtered table by VoucherARBusinessKeySurrogate
var VOD =
    SUMMARIZE(
        IL12M,
        DIM_VoucherAR[VoucherARBusinessKeySurrogate]
    )
// Return the sum of 'TaxBaseAmountCur' from the filtered table IL12M, applying an additional filter for specific document types
RETURN
    CALCULATE(
    SUMX(
        VOD,
        [TaxBaseAmountCur]
    ),
    DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}
)

 

Please give me some support. Thanks a lot.

 

DaxQuery Total
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Hi GeorgWildmoser 

     

    There is a general method to solve the problem that the total does not equal the breakdown, you can refer to it.

     

    Create New Measure = 
SUMX(
    CROSSJOIN(
        VALUES('Table'[Row Field1]),
        VALUES('Table'[Row Field2]),
        VALUES('Table'[Row Field3]),
        ...
    ),
    [Old Measure]
)

     

    According to your screenshot, you should fill the first 6 columns into VALUES in order.

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

1 Reply

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    Hi GeorgWildmoser 

     

    There is a general method to solve the problem that the total does not equal the breakdown, you can refer to it.

     

    Create New Measure = 
SUMX(
    CROSSJOIN(
        VALUES('Table'[Row Field1]),
        VALUES('Table'[Row Field2]),
        VALUES('Table'[Row Field3]),
        ...
    ),
    [Old Measure]
)

     

    According to your screenshot, you should fill the first 6 columns into VALUES in order.

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~