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.
