Forum Discussion

dclaudio's avatar
dclaudio
Frequent Visitor
4 years ago

Dax Help

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 (Pr...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

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's avatar
dclaudio
Frequent Visitor
4 years ago

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's avatar
    lbendlin
    Super User
    4 years ago

    Please provide sanitized sample data that fully covers your issue.