Filter Measure by different date columns

[Toots]

Hi All,

I am setting up some sales pipeline reporting where on one visual

I would like to have the only sales with the likelihood "WON" filtered by the actual close date

Then have a target of sales filtered by the exp close date

And set an over target the manager will set on a target table.

The relationship to the date table is done by the start date

Any thoughts on a filtered measure or any other way?

[v-alq-msft]

Hi, @Toots 

I'd like to suggest you use the USERELATIONSHIP DAX function to make the inactive relationship active during the evaluation of a model calculation.

For further information, you may refer to the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Toots]

Hi There, The userelationship function works perfect thank you. Do you know if you can also use the filter function within this so I can filter out WON and Deleted out of the likelihood column?

AU Market Sales EC $ =

CALCULATE (

SUM ( 'AU Market Map'[Sales $] ),

FILTER(ALL('AU Market Map','AU Market Map'[Likelihood]="Target","Business Case","Early Days","Possible","Strong"))

USERELATIONSHIP ( 'Calendar'[Date], 'AU Market Map'[Exp Close Date ] )

)

[amitchandak · Accepted solution]

@Toots , You can join both dates with date table and use userelation to switch the relation.

Please find the example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...