Dax Help

[dclaudio]

I am trying to calculate the time it takes one of my customers to go from no revenue to meeting the revenue projection we create when signing them up.  I have numbers for what the revenue goal is (Prorated Booking) and the actual revenue to date (Calc Total Revenue) by day for each customer (CID).  I need help with the dax to count the days between sign-up to met/exceeded projected revenue.  

I should add that as mentioned, I have the actual revenue broken out by day for each CID, as such:  

[lbendlin]

Is the prorated booking in k$ ?

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[dclaudio]

The numbers are exactly as posted.  Nothing in K's or M's.  

Expected outcome would be a number (in days) that is took for each customer to have the $Calc Total Rev meet or exceed the Prorated Booking Value

CID		Prorated Booking		$ Calc Total Revenue		Period Name		Date Integrated
86235		343.6192		10.11		1/26/2021 0:00		1/15/2021 0:00
86235		343.6192		14.01		1/31/2021 0:00		1/15/2021 0:00
86235		343.6192		16.87		2/7/2021 0:00		1/15/2021 0:00
86354		341.9178		51		2/14/2021 0:00		2/12/2021 0:00
86354		341.9178		52.85		2/16/2021 0:00		2/12/2021 0:00
86354		341.9178		52.81		2/19/2021 0:00		2/12/2021 0:00
86235		343.6192		11.54		2/21/2021 0:00		1/15/2021 0:00
86354		341.9178		51.27		2/21/2021 0:00		2/12/2021 0:00
86235		343.6192		9.98		2/28/2021 0:00		1/15/2021 0:00
86235		343.6192		9.98		3/7/2021 0:00		1/15/2021 0:00
86354		341.9178		52.1		3/10/2021 0:00		2/12/2021 0:00
86235		343.6192		10.5		3/14/2021 0:00		1/15/2021 0:00
86354		341.9178		52.31		3/14/2021 0:00		2/12/2021 0:00
86235		343.6192		14.66		3/19/2021 0:00		1/15/2021 0:00
86235		343.6192		9.65		3/28/2021 0:00		1/15/2021 0:00
86235		343.6192		16.61		4/4/2021 0:00		1/15/2021 0:00
86235		343.6192		13.62		4/11/2021 0:00		1/15/2021 0:00
86235		343.6192		10.76		4/25/2021 0:00		1/15/2021 0:00
86235		343.6192		12.43		4/28/2021 0:00		1/15/2021 0:00
86235		343.6192		9.61		5/23/2021 0:00		1/15/2021 0:00
86235		343.6192		17.39		5/30/2021 0:00		1/15/2021 0:00
86235		343.6192		11.54		6/6/2021 0:00		1/15/2021 0:00
86235		343.6192		10.97		6/13/2021 0:00		1/15/2021 0:00
86235		343.6192		13.1		6/18/2021 0:00		1/15/2021 0:00
86235		343.6192		11.67		6/25/2021 0:00		1/15/2021 0:00
86235		343.6192		11.54		7/11/2021 0:00		1/15/2021 0:00
86235		343.6192		11.28		8/1/2021 0:00		1/15/2021 0:00
86354		341.9178		51.69		8/5/2021 0:00		2/12/2021 0:00
86235		343.6192		11.54		8/8/2021 0:00		1/15/2021 0:00
86235		343.6192		15.96		8/15/2021 0:00		1/15/2021 0:00
86354		341.9178		56.2		9/5/2021 0:00		2/12/2021 0:00
86354		341.9178		51		9/7/2021 0:00		2/12/2021 0:00
86354		341.9178		56.2		9/19/2021 0:00		2/12/2021 0:00

Here is an sql equivalent that might help explain what im looking for:  

SELECT
Church_Id
, Date
, Amount
, RunningTotal = SUM(Amount) OVER (PARTITION BY Church_Id ORDER BY Church_Id, Date)
FROM
transactions
GROUP BY
Church_Id
, Date
, Amount
ORDER BY
Church_Id
, Date

[lbendlin]

Create two measures

RT = 
var d = SELECTEDVALUE('Table'[Period Name])
return sumx(filter(ALLEXCEPT('Table','Table'[CID]),'Table'[Period Name]<=d ), 'Table'[$ Calc Total Revenue])

# days = 
var mind = calculate(min('Table'[Period Name]),ALLEXCEPT('Table','Table'[CID]))
var reached = calculate(min('Table'[Period Name]),filter(ALLEXCEPT('Table','Table'[CID]),[RT]>='Table'[Prorated Booking]))
return DATEDIFF(mind,reached,DAY)

Add the second measure to the CID in a table visual

[dclaudio]

That got me close, but I get:  A single value for column 'Prorated Booking' in table 'Sheet1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I should have said in the original post that Im working off two different tables here.  All the fields are within one table except for the Prorated Bookings, which is another.  Would a merge querys option fix that issue?

[lbendlin]

Please provide sanitized sample data that fully covers your issue.