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 (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:  

 

 

 

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

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

      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'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