Filtering my Table visual by comparing 2 Date Fields

[waqarqtrz]

I currently have 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'VE 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

[Anonymous]

Hi @waqarqtrz ,

Please try these measures:

$ External2 = 
VAR __min_date = MIN('dbDates'[Date])
VAR __max_date = MAX('dbDates'[Date])
VAR __result = CALCULATE([$ External], ('TMO_Table Data'[TransactionDate]<=__min_date && 'TMO_Table Data'[PaidOffDate]>=__max_date) || 'TMO_Table Data'[PaidOffDate]=BLANK())
RETURN
    __result

$ Internal2 = 
VAR __min_date = MIN('dbDates'[Date])
VAR __max_date = MAX('dbDates'[Date])
VAR __result = IF( NOT ISBLANK([$ External2]), CALCULATE([$ Internal] + 0, ('TMO_Table Data'[TransactionDate]<=__min_date && 'TMO_Table Data'[PaidOffDate]>=__max_date) || 'TMO_Table Data'[PaidOffDate]=BLANK()))
RETURN
    __result

$ Total = [$ External2] - [$ Internal2]

If I misunderstood your question, please feel free to contact me.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MFelix]

Hi @waqarqtrz ,

What you need is to create a measure that gets the calculated value for you.

I assume you have a calendar table on your model, if not you need one to do the calculation then just add a measure similar to this one:

Total Value = CALCULATE(SUM(TMO_Table[$ Amount]), TMO_Table[TransactionDate] >= MIN('Calendar'[Date]) && TMO_Table[TransactionDate] <= MAX('Calendar'[Date]))

This should give you the interaction between the values and the slicer that you need.

The creation of a column in your model is not interactive with selection in slicers in this caswe you min(date) will always get the same value and not be dynamic