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.
I am trying to accomplish this scenario:
My table is sorted by Created Date. I want to create a new column 'New Qty' where the new qty will be the MAX value of all the previous values up to that point by Customer. Is this possible?
Name | CreatedDate | Total FLDAPP | New Qty |
Customer 1 | 8/23/2016 | 9 | 9 |
Customer 1 | 9/1/2016 | 10 | 10 |
Customer 1 | 10/29/2016 | 11 | 11 |
Customer 1 | 11/23/2016 | 13 | 13 |
Customer 1 | 12/1/2016 | 15 | 15 |
Customer 2 | 11/9/2016 | 12 | 12 |
Customer 2 | 12/1/2016 | 9 | 12 |
Customer 2 | 1/18/2017 | 10 | 12 |
Customer 2 | 2/3/2017 | 14 | 14 |
Customer 2 | 3/5/2017 | 12 | 14 |
Customer 2 | 4/6/2017 | 13 | 14 |
Customer 2 | 5/2/2017 | 15 | 15 |
Customer 2 | 6/5/2017 | 10 | 15 |
Customer 3 | 7/4/2017 | 12 | 12 |
Customer 3 | 8/3/2017 | 11 | 12 |
Customer 3 | 9/2/2017 | 10 | 12 |
Customer 3 | 10/2/2017 | 9 | 12 |
Customer 3 | 11/1/2017 | 3 | 12 |
Customer 3 | 12/4/2017 | 3 | 12 |
This is what I have so far
New Qty = CALCULATE(MAX(FLDAPP[Total FLDAPP]),ALLEXCEPT(FLDAPP,FLDAPP[Name])) but it is returning just the MAX for the whole Customer group
Name | CreatedDate | Total FLDAPP | New Qty |
Customer 1 | 8/23/2016 | 9 | 15 |
Customer 1 | 9/1/2016 | 10 | 15 |
Customer 1 | 10/29/2016 | 11 | 15 |
Customer 1 | 11/23/2016 | 13 | 15 |
Customer 1 | 12/1/2016 | 15 | 15 |
Customer 2 | 11/9/2016 | 12 | 15 |
Customer 2 | 12/1/2016 | 9 | 15 |
Customer 2 | 1/18/2017 | 10 | 15 |
Customer 2 | 2/3/2017 | 14 | 15 |
Customer 2 | 3/5/2017 | 12 | 15 |
Customer 2 | 4/6/2017 | 13 | 15 |
Customer 2 | 5/2/2017 | 15 | 15 |
Customer 2 | 6/5/2017 | 10 | 15 |
Customer 3 | 7/4/2017 | 12 | 12 |
Customer 3 | 8/3/2017 | 11 | 12 |
Customer 3 | 9/2/2017 | 10 | 12 |
Customer 3 | 10/2/2017 | 9 | 12 |
Customer 3 | 11/1/2017 | 3 | 12 |
Customer 3 | 12/4/2017 | 3 | 12 |
Thank you in Advance!
Solved! Go to Solution.
Hi @Anonymous,
Try with this calculated column:
New Qty = VAR CurrentDate = Table1[CreatedDate] VAR CurrentName = Table1[Name] VAR NewQty = CALCULATE( MAX( Table1[Total FLDAPP] ); FILTER( Table1; Table1[CreatedDate] <= CurrentDate && Table1[Name] = CurrentName ) ) RETURN NewQty
Regards!
Thank you! @luxpbi I just came up with this solution
I created this measure
New QTY =
VAR EarlierTime =
CALCULATETABLE(
FLDAPP,
FILTER (
ALLSELECTED ( FLDAPP[CreatedDate] ),
FLDAPP[CreatedDate] <= SELECTEDVALUE ( FLDAPP[CreatedDate] )
)
)
RETURN
MAXX(EarlierTime,FLDAPP[Total FLDAPP])
also thank you to @Zubair_Muhammad for this post https://community.powerbi.com/t5/Desktop/Moving-Range-DAX-subtract-previous-row-values-from-earlier/...
Hi @Anonymous,
Try with this calculated column:
New Qty = VAR CurrentDate = Table1[CreatedDate] VAR CurrentName = Table1[Name] VAR NewQty = CALCULATE( MAX( Table1[Total FLDAPP] ); FILTER( Table1; Table1[CreatedDate] <= CurrentDate && Table1[Name] = CurrentName ) ) RETURN NewQty
Regards!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |