Forum Discussion
waqarqtrz
1 year agoFrequent Visitor
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 Code | Loan Name | $ External | $ Internal | $ Total |
| 1111 | Loan A | $10,000 | $0 | $10,000 |
| 2222 | Loan B | $0 | $0 | $0 |
| 3333 | Loan 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 Code | Loan Name | Participant Type | PaidOffDate | TransactionDate | $ Amount |
| 1111 | Loan A | External Bank A | February 2, 2024 | June 6, 2023 | $10,000 |
| 1111 | Loan A | External Bank A | February 2, 2024 | March, 1, 2024 | -$10,000 |
| 2222 | Loan B | Internal Entity A | November 1, 2023 | January 1, 2023 | $20,000 |
| 2222 | Loan B | Internal Entity A | November 1, 2023 | November 1, 2023 | -$20,000 |
| 3333 | Loan C | External Bank B | August 24, 2023 | $30,000 | |
| 3333 | Loan C | External 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 Code | Loan Name | $ External | $ Internal | $ Total |
| 1111 | Loan A | $10,000 | $0 | $10,000 |
| 3333 | Loan C | $25,000 | $0 | $25,000 |
Please let me know if you need more details or specific information as I'm relatively new.
1 Reply
- lbendlin
Super User