Create DAX function using multiple dates from same table

[mrawlings]

Hello, I am new to posting to this forum so I am not sure the best way to do it.  It would be easiest for me to upload a pbix file and then describe my issue while referencing the file.  Is that possible?  I don't see the ability to attach a file to the post.

Thanks for the help.

Mark

[MattAllington]

Put the file on dropbox and post the link. 

[mrawlings]

Thanks Matt. I appreciate the response.  I should have thought of that.  Attached is the link.

https://www.dropbox.com/sh/h4fecgic0ueubbp/AACLMeq7wXvwNZYrOE4GflwFa?dl=0

I am trying to calculate a customer churn % that takes the numnber of customers lost in one year divided by the total number of customers on the first day of the year.  Due to the different date columns needed in the calculation (customer start date, customer end date, and calendar tables, I can't figure out how to make it work.  I have tried different combinations using calendar tables (both connected and unconnected), but I can't figure it out.  

As can be seen in the PBIX file, I have a formula for the total number of customers on the first day of year.  I also have a formula for customers lost during the year ( I'm not sure if I used the best formulas but they seem to be doing what I want.)  Now I just need a way to divide the number of lost customers by the total customers as of the beginning of the year (for example, for 2016, the customer churn is 4 / 103 or 3.88%.

Any advice is greatly appreciated.

Thanks, Mark

[MattAllington · Accepted solution]

OK, have a read of my article here  https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/ 

It is not the same example of course but it is a similar situation where you have a start date and end date and you need to filter based on both to work out who is in and how is out.  I suggest first write a measure that gives you a count of customers at the start of the year.  Then write a measure that gives you a count of new customers during the year.  Then write one that gives you a count at the end of the year.  From there you should be able to work out what you need.

Let me know if you need help

[mrawlings]

Thanks a lot Matt.  I was able to work it out based on your suggestions.  I appreciate your help and the many blog postings that you put out that help us all in our journey to learn DAX / Power Pivot.