Dax Query Incorrect Total

GeorgWildmoser — Fri, 14 Jun 2024 11:17:11 GMT

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.

Re: Dax Query Incorrect Total

xifeng_L — Sat, 15 Jun 2024 08:54:45 GMT

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~

topic Dax Query Incorrect Total in DAX Commands and Tips

Dax Query Incorrect Total

Re: Dax Query Incorrect Total