topic Probably problem of a dax formula in combination of the model in DAX Commands and Tips

Probably problem of a dax formula in combination of the model

GeorgWildmoser — Fri, 14 Jun 2024 12:10:12 GMT

Hello,

I would like to create a report that shows me receivables due after 90 days. The current model has several calendar tables. One for due date and so on. There are no plans yet to change the model.

I use a Dax query to determine the receivables concerned and then filter them dynamically using a different calendar. This works statically, but no longer dynamically. The query works in the DaxViewer, but no longer in the report.

To fix this, I do not understand the underlying problem. 😞 I would be very happy to receive help.

Here is the result:

This result would expected:

The model:

The DAX:

Netto (12M v.DocDate Ü90T v.DueDate) =

// Determine the maximum date in the 'TimeIfDocumentElsePostingDate' table

var maxDate = MAX('TimeIfDocumentElsePostingDate'[Date Date])

// Get the current date minus one day

var aktDate = TODAY()-1

// Use the maxDate if it is less than or equal to the current date minus one, otherwise use the current date minus one

var useDate = IF(maxDate <= aktDate, maxDate, aktDate)

// Create a table of dates within the last year from the useDate

var DateTbl = DATESINPERIOD('TimeIfDocumentElsePostingDate'[Date Date], useDate, -1, YEAR)

// Calculate a table with additional columns for DueDate plus 90 days and adjusted CloseDate

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]

"@DueDatePlus90", DATEADD(TimeDueDate[Date Date], 90, DAY), // Add 90 days to the DueDate

"@CloseDateCur", IF(

ISBLANK(TimeCloseDate[Date Date]) || TimeCloseDate[Date Date] > useDate,

useDate, // If CloseDate is blank or greater than useDate, use useDate

TimeCloseDate[Date Date] // Otherwise, use CloseDate

)

DateTbl, // Filter by the DateTbl period

NOT ISBLANK('TimeDueDate'[TimeBusinessKeySurrogate]), // Ensure DueDate is not blank

DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"} // Filter by specific document types

)

// Filter the IL12M table for rows where DueDate plus 90 days is less than CloseDateCur

var OD90DP =

FILTER(

IL12M,

[@DueDatePlus90] < [@CloseDateCur]

)

// Summarize the filtered table by VoucherARBusinessKeySurrogate

var VOD90DP =

SUMMARIZE(

OD90DP,

DIM_VoucherAR[VoucherARBusinessKeySurrogate]

)

// Calculate the sum of TaxBaseAmountCur for the summarized VOD90DP table, filtered by the specific document types

RETURN

CALCULATE(

SUMX(

VOD90DP,

[TaxBaseAmountCur]

DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}

)

Re: Probably problem of a dax formula in combination of the model

GeorgWildmoser — Fri, 14 Jun 2024 12:21:15 GMT

A small addition. A second Dax query with a similar structure works. It uses a different date field for one variable, but ultimately only produces filtered documents. I do not understand the underlying problem.

The second DAX formula:

Netto bez (12M v.DocDate Ü90 v.DueDate) =

VAR maxDate = MAX('TimeIfDocumentElsePostingDate'[Date Date])

-- Get the maximum date from the 'TimeIfDocumentElsePostingDate' table

VAR aktDate = TODAY() - 1

-- Calculate the current date minus one day

VAR useDate = IF(maxDate <= aktDate, maxDate, aktDate)

-- Determine the date to use, either the maximum date or the current date minus one day, whichever is smaller

VAR DateTbl = DATESINPERIOD('TimeIfDocumentElsePostingDate'[Date Date], useDate, -1, YEAR)

-- Create a table of dates covering the last year up to the useDate

VAR IaPL12M =

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(

CUB_AR,

DIM_Dim5[Dimension5BusinessKeySurrogate],

DIM_VoucherAR[VoucherARBusinessKeySurrogate],

TimeCloseDate[Date Date],

TimeDueDate[Date Date]

"@DueDatePlus90", DATEADD(TimeDueDate[Date Date], 90, DAY),

-- Add a column calculating the due date plus 90 days

"@CloseDateCur", IF(

ISBLANK(TimeCloseDate[Date Date]) || TimeCloseDate[Date Date] > useDate,

useDate,

TimeCloseDate[Date Date]

)

-- Add a column to get the close date or useDate if the close date is blank or after useDate

DateTbl,

-- Filter the data to only include dates from the last year

NOT ISBLANK('TimeDueDate'[TimeBusinessKeySurrogate]),

-- Ensure the due date is not blank

NOT ISBLANK(TimeCloseDate[TimeBusinessKeySurrogate]),

-- Ensure the close date is not blank

DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}

-- Filter for specific document types

)

VAR OD90DP =

FILTER(

IaPL12M,

[@DueDatePlus90] < TimeCloseDate[Date Date]

-- Filter for invoices that are overdue by more than 90 days

)

// Summarize the filtered table by VoucherARBusinessKeySurrogate

var VOD90DP =

SUMMARIZE(

OD90DP,

DIM_VoucherAR[VoucherARBusinessKeySurrogate]

)

RETURN

CALCULATE(

SUMX(

VOD90DP,

[TaxBaseAmountCur]

-- Sum the tax base amount for the filtered overdue invoices

DIM_VoucherAR[VoucherARDocumentTypeShort] IN {"FTG", "GS", "RG", "FTR"}

-- Ensure the calculation is for the specified document types

)

Re: Probably problem of a dax formula in combination of the model

Anonymous — Mon, 17 Jun 2024 02:22:50 GMT

Hi GeorgWildmoser

Do you want the data in the last column of the table to change dynamically with the slicer?

I'm a little confused about your expected result, as the screenshot you provided don't seem to change except for the total slicer range.

Since I don't have your data, I can't restore your scenario, below is a simple sample I created for your reference.

Sample：

Slicer table:

no relationship between two tables:

I created a measure that returns 1 if the date is greater than or equal to 90 days in the range selected by slicer.

Measure = VAR _MinDate = MIN(Slicer[Date]) VAR _MaxDate = MAX(Slicer[Date]) RETURN IF(MAX([Date]) >= _MaxDate + 90, 1, 0)

Output:

If your problem persists, please provide some sample data so I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.