Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to calculate the time when a customer hits hits $25000 mark on his account.
I have the following fields:
1. Customer ID:- Uniquely defining the customer
2. Date Account Opened:- the date when the account was opened
2. Invoice date : Dates when customer places order
4. Price: Amount charged to the customer for that order
Table looks like below:
Customer ID | Date Account Opened | Invoice Date | Price |
111 | 8/3/2017 | 9/4/2017 | $ 3,424 |
627 | 3/5/2018 | 4/4/2018 | $ 10,000 |
111 | 8/3/2017 | 5/2/2018 | $ 15,892 |
339 | 6/9/2017 | 8/10/2017 | $ 17,029 |
339 | 6/9/2017 | 2/4/2019 | $ 8,712 |
How should I do this in DAX?
Solved! Go to Solution.
-- base measure [Total] = SUM ( T[Price] )
[Target Hit Date] = // This target value could be taken from a slicer // instead of hard-coding it to allow maximum
// flexibility. var __target = 25000 var __cummulativeTotals = ADDCOLUMNS ( VALUES ( T[Invoice Date] ), "CumulTotal", var __invDate = T[Invoice Date] var __cumulativeTotal = calculate ( [Total], T[Invoice Date] <= __invDate ) return __cummulativeTotal ) var __hitDate = minx ( filter( __cummulativeTotals, [CumulTotal] >= __target ), T[Invoice Date] ) return __hitDate
If your model has only this one table and does not have a proper Date table connected to it... then you should CHANGE THE MODEL and make it correct. If you don't want to suffer later on... but it's your call.
Of course, the above measure works for any set of customers, not only for one.
Best
Darek
var __cummulativeTotals = -- this one has an "s" on the end... ADDCOLUMNS ( VALUES ( T[Invoice Date] ), "CumulTotal", var __invDate = T[Invoice Date] var __cummulativeTotal = -- this one does not... and it's had one "m" before calculate ( [Total], T[Invoice Date] <= __invDate ) return __cummulativeTotal -- and this one does not... )
Best
Darek
-- base measure [Total] = SUM ( T[Price] )
[Target Hit Date] = // This target value could be taken from a slicer // instead of hard-coding it to allow maximum
// flexibility. var __target = 25000 var __cummulativeTotals = ADDCOLUMNS ( VALUES ( T[Invoice Date] ), "CumulTotal", var __invDate = T[Invoice Date] var __cumulativeTotal = calculate ( [Total], T[Invoice Date] <= __invDate ) return __cummulativeTotal ) var __hitDate = minx ( filter( __cummulativeTotals, [CumulTotal] >= __target ), T[Invoice Date] ) return __hitDate
If your model has only this one table and does not have a proper Date table connected to it... then you should CHANGE THE MODEL and make it correct. If you don't want to suffer later on... but it's your call.
Of course, the above measure works for any set of customers, not only for one.
Best
Darek
Hello Derek,
Thank you for your fast response.
I tried to run this but I am getting an error that __cummulativeTotals is not a valid table, variable or funtion name ( after the 'return' statement).
I do have a Date table in my model connected to this table.
Thanks,
Mansi
var __cummulativeTotals = -- this one has an "s" on the end... ADDCOLUMNS ( VALUES ( T[Invoice Date] ), "CumulTotal", var __invDate = T[Invoice Date] var __cummulativeTotal = -- this one does not... and it's had one "m" before calculate ( [Total], T[Invoice Date] <= __invDate ) return __cummulativeTotal -- and this one does not... )
Best
Darek
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |