Utilize Multiple Date Columns to affect Measures driven by Slicer

[Anonymous]

I'm trying to create a measure that utilizes multiple date columns that don't have an active relationship with the Date table. My model is this:

Relationships:

• Active
  • Date table to the Churn table (Date[Date] --> Churn[OpportunityCloseDate])
  • Account table to the Churn table (Account[AccountName] --> Account[AccountName])
• Inactive
  • Account table to the Date table (Account[CustomerSince] --> Date[Date])
  • Account table to the Date table (Account[CancellationDate] --> Date[Date])

I have a date slicer that uses Date[Date] and will change the results if Churn[OpportunityCloseDate]  is used in the visualization. No issue there.

What I need to do is to utilize the date slicer so that is affects the results based on Account[CustomerSince] and Account[CancellationDate] in a DAX measure. I need to count the number of Accounts in the Accounts table that fall in the date range from the date slicer that have a CustomerSince date in that range AND a CancellationDate in that range, regardless if the date value is blank.

An ideal would result would be something like:

• We have 10 accounts that have have a CustomerSince value that is in the date range.
• Of those 10 accounts, 5 accounts have a CancellationDate that is blank or in the future (outside the date range)
• The other 5 have a CancellationDate that is in the date range.
• We would then take the 5 accounts that are meet the CancellationDate criteria and divide that into the 10 accounts. Our final result would be 0.50 or 50% churn rate.

Is this even possible? Thanks for the help.

[DallasBaba · Accepted solution]

Kindly download the pbix Sample88 Solution on GitHub

https://github.com/DallasBaba/DaxSolutions/commit/f2397d009bba8709a292672a15d838fe0e028cf2

or 

https://we.tl/t-0u0VP1uXoa 

Solution Approach :
 1 - Reconfigure the Relationship btw the DateTable, OpportunitiesTable, AccountsTable
 2 - Create new measures to filter the Active Account, Cancelled Accounts & Churn Rate

Solution Delivery: 
 3 - The DateTable is now filtering both the Accounts & Opportunities Table

I hope these meet your request.  Kindly give it a kudos by clicking the Thumbs Up! & Accept it as a solution to help the other members find it more quickly.

Best Regards,

Dallas.

[Anonymous]

@DallasBaba Now that I am looking at my sample.pbix file, I noticed that my table that is showing the dates from the Accounts table is not actually filtering (at first glane, I thought it was). 

[Anonymous]

@DallasBaba Thanks but I tried a different approach and I think it's doing what I want it to:

I set up the relationship as:

I have each relationship set to cross filter in both directions. I'm attaching my sample .pbix if you want to take a look but it looks to be filtering my tables as I expect, though my measures for TotalActiveAccounts and CancelledAccounts are not producing correct results from what I can tell so I have to keep messing with that.

Let me know if you think I have the right approach. I appreciate the suggestion because I think the cross filter was my initial issue. Not sure if this link will work: sample.pbix 

[DallasBaba]

@Anonymous Let's start by changing the data modeling. Can you do the following
1 - Remove the DateTable relationship from The OpportunityTable
2 - Change the cross filter direction of Account & OpportunityTable to BOTH DIRECTION

See the image below for an example.

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!   

Note: You can send a Pbix file with sample data and more info on what you are trying to do.

Thanks

Dallas