do I need I dax measure(?) for a event based on timeline- and how?

[Cool_McCool]

Hi.
I have a problem and I am certain others have had it but after searching for a week I am coming to you. I

We are analysing our offer success rates.

A customer may or may not order aginst the offer number and they may use a different customer number and/or manual entry with no link to the offer (Don't ask we have many processes and 2 ERPs )

I have so no problem linking the tables (I love powerpivot). I am having problems with the timeline. I need the right series -> offer ->order ->invoice to know if the offer was sucessful. The timeframe should be 12 months rolling from date of offer.

I have a couple of sets of data.
1. List of customer number (multiple numbers for one client)
2. List of offers made (incl. customer no. product qty and date)
3. List of orders (incl. customer no. product qty and date)
4. List of invoices (incl. customer no. product qty and date, values)

The end of my pivot needs to be something like:

Client Name	No. of itemlines offered (product number)	number of orders placed after offer date	success%	number of the orders have been invoiced	value
no problem	no problem	countif offer date before order date?	no problem	countif offer date before ínv. date?	countif offer date before ínv. date?
Alice	14	7	50%	4	300
Brad	20	5	25%	5	1000
Cindy	2	0	0%	0	0
Doug	50	10	20%	1	600

<tbody>
</tbody>

If somebody has a better idea of figuring out which customer is causing "alot of work and not coming through" - we'd be game.

Thanks
Chris

 

[scottsen]

Crisp this up for me: How do I know I have "a success" ? Crispser the better... like "the Order[OrderDate] for > Offer[OrderDate]" and ".......".

 

[Cool_McCool]

Hi Scott, thanks for the question. Well basically success is if the product is ordered with 12 months after the offer is made. But I guess I had the answer to the problem in the question, silly me.
I will take the time to learn more about dealing with dates and post my solution.

Cheers
Chris