Create a measure removing date filters but using them to filter another date column

[Priscila_Vindas]

Hi!

I'm reporting on leads and actions they do, let's focus on one action: paying a fee.
In a table I have: Created Date of the lead (Connected to a calendar table), lead id, and (if applicable) the date in which the lead paid a fee.

On my sheet, I'm filtering based on the Calendar connection (Creation of the lead).

For a specific card, I want this measure: number of people who paid the fee during the days on the slicer. This means: the created date of the lead removed from the filters (skip the slicer), but if the paid fee date is between the dates in the slicer, if should be counted.

I cannot change the slicer value, since I'm going to do the same with several lead actions on the same sheet. So I need to figure out a measure to do this for all the actions.

Thanks a lot for your help!!

[Priscila_Vindas]

Nice! The use relationship worked perfectly for me! Thanks!!!

[Anonymous]

Hi @Priscila_Vindas 

From my understanding, you have 2 date columns in your table .

1. Created date of the lead

2. The date in which the lead paid a fee

Count of Lead = 
CALCULATE (
    DISTINCTCOUNT ( Sheet1[Lead ID] ),
    ALLEXCEPT ( Sheet1, Sheet1[Date Lead Paid a A fee] )
)

Regards

Dax_Noob

[amitchandak · Accepted solution]

@Priscila_Vindas , Based on what I got, You can create a join of date table with all dates few join will be inactive, but you can activate using userelationship in measure

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[CreateDate], 'Date'[Date]))

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[paidDate], 'Date'[Date]))

Another way is to have an independent date table, then you can manipulate dates easily

//Date1 is independent Date table
new measure = //between
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Created Date] >=_min && 'Table'[Date] <=_max))

//Date1 is independent Date table
new measure = //equal to max
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI