Forum Discussion

waqarqtrz's avatar
waqarqtrz
Frequent Visitor
1 year ago

How to filter my Table visual by comparing 2 Date Fields using Calculated Columns?

I currently have the following visuals as follows:

 

Date Filter (slider in between two dates) from my dedicated Date Table and field: dbDates[Date]:

  • There's a Min value and Max value
  • Assume the filters are set between December 1, 2023 and December 31, 2023.

 

Table Visual (using the TMO_Table Data (below):

Loan CodeLoan Name$ External$ Internal$ Total
1111Loan A$10,000$0$10,000
2222Loan B$0$0$0
3333Loan C$25,000

$0

$25,000
  • The $ values above exclude the min(dbDates[Date]) as I want it to be a cumulative SUM regardless of when the beginning date is.
  • The $ values are also measures that filter the investor type through some logic in their categories and sums it up.

TMO_Table Data

Loan CodeLoan NameParticipant TypePaidOffDateTransactionDate$ Amount
1111Loan AExternal Bank AFebruary 2, 2024June 6, 2023$10,000
1111Loan AExternal Bank AFebruary 2, 2024March, 1, 2024-$10,000
2222Loan BInternal Entity ANovember 1, 2023January 1, 2023$20,000
2222Loan BInternal Entity ANovember 1, 2023November 1, 2023-$20,000
3333Loan CExternal Bank B August 24, 2023$30,000
3333Loan CExternal Bank B December 14, 2023-$5,000

 

What I have been trying to do:

  • Create a filter that can be applied to the entire Power BI Page. I realize I can't do this through a measure so I had to create a column in the Table View.
  • This filter will apply a logic as follows:

IF PaidOffDate is BLANK

    then "INCLUDE",

    Else:

       IF PaidOffDate >= MIN(dbDates[Date]),

       then "INCLUDE"

       else: "PAID OFF"

 

Desired Output in my Table Visual: assuming the date is still filtered between December 1, 2023 and December 31, 2023:

Loan CodeLoan Name$ External$ Internal$ Total
1111Loan A$10,000$0$10,000
3333Loan C$25,000

$0

$25,000


Please let me know if you need more details or specific information as I'm relatively new.