Forum Discussion
Dax Help
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.
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
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
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?
Please provide sanitized sample data that fully covers your issue.