My calculated column does not seem to be correctly comparing 2 dates column?

[waqarqtrz]

I have a dbDates table that i created through a blank query in Power Query

I have a c177 TMO table that contains loan data

I have a date filter in my report that is in the form of a slider - you can choose the beginning and end dates.

I am trying to create a calculated column as follows:

"StartDateMeasure = MIN(dbDates[CorrectedDate])"

"

FILTER_PaidOffDate =

VAR StartDate = [StartDateMeasure]

RETURN

IF(

    ISBLANK(MAX('c177 TMO'[PaidOffDate])) || MAX('c177 TMO'[PaidOffDate]) >= StartDate,

    "Include",

    "Repaid Loan"

)"

However, it seems to still have paid off dates before the Start Date as "Included" instead of Repaid.

When I explicitly compare it against DATE(Year of MIN Start Date, Month of MIN start date etc...) it seems to do a better job.

I've double checked that both Dates I am trying to compare against ate in the the date format, and the PaidOffDate does have blanks in it since they are not paid off yet.

Sample Data:

C177 TMO:

LoanCode	TransactionDate	PaidOffDate	TransactionAmount
2111/24	2023-11-30	2024-01-01	$150
2111/24	2024-01-01	2024-01-01	$-24000
1994/22	2023-10-20	2023-11-20	$80
1994/22	2023-11-20	2023-11-20	$-10000
2222	2023-11-01		$250
2222	2024-02-25		$150

Then I have a standard Date table dbDates with a Date column, Day Name, Day of Month, Month Name, Month-Year etc.

Output Wanted:

IF Date filter is set between 2024-01-01 and 2024-01-31

C177 TMO:

LoanCode	TransactionDate	PaidOffDate	TransactionAmount	FILTER_PaidOffDate
2111/24	2023-11-30	2024-01-01	$150	Include
2111/24	2024-01-01	2024-01-01	$-24000	Include
1994/22	2023-10-20	2023-11-20	$80	Repaid Loan
1994/22	2023-11-20	2023-11-20	$-10000	Repaid Loan
2222	2023-11-01		$250	Include
2222	2024-02-25		$150	Include

[ryan_mayu · Accepted solution]

@waqarqtrz 

you can try this

Measure = if(max('C177 TMO'[PaidOffDate])>=min('Date'[Date])&&max('C177 TMO'[PaidOffDate])<=max('Date'[Date]),"Include","Repaire Loan")

[ryan_mayu · Accepted solution]

@waqarqtrz 

you can try this

Measure = if(max('C177 TMO'[PaidOffDate])>=min('Date'[Date])&&max('C177 TMO'[PaidOffDate])<=max('Date'[Date]),"Include","Repaire Loan")

[waqarqtrz]

why wouldn't this work:

FILTER_PaidOffDate =

VAR StartDate = [StartDateMeasure]

RETURN

IF(

    ISBLANK(MAX('c177 TMO'[PaidOffDate])) || MAX('c177 TMO'[PaidOffDate]) >= StartDate,

    "Include",

    "Repaid Loan"

)
If i want to also include blank PaidOffDate in the criteria as well?

[ryan_mayu · Accepted solution]

if you want to use DAX to create a new table. The filter visual will not affect the table output.

[ryan_mayu]

could you pls provide some sample data and expected output?

[waqarqtrz]

Provided some sample data in my original question.

[ryan_mayu]

So you have another date table. Then what's the expected output based on the sample data you provided?

[waqarqtrz]

Just realized my sample output did not get recognized - it is now included and formatted as well in a table.