cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbelandria
Frequent Visitor

New Column to calculate MAX up to that point

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? 

 

NameCreatedDateTotal FLDAPPNew  Qty
Customer 18/23/201699
Customer 19/1/20161010
Customer 110/29/20161111
Customer 111/23/20161313
Customer 112/1/20161515
Customer 211/9/20161212
Customer 212/1/2016912
Customer 21/18/20171012
Customer 22/3/20171414
Customer 23/5/20171214
Customer 24/6/20171314
Customer 25/2/20171515
Customer 26/5/20171015
Customer 37/4/20171212
Customer 38/3/20171112
Customer 39/2/20171012
Customer 310/2/2017912
Customer 311/1/2017312
Customer 312/4/2017312

 

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

NameCreatedDateTotal FLDAPPNew  Qty
Customer 18/23/2016915
Customer 19/1/20161015
Customer 110/29/20161115
Customer 111/23/20161315
Customer 112/1/20161515
Customer 211/9/20161215
Customer 212/1/2016915
Customer 21/18/20171015
Customer 22/3/20171415
Customer 23/5/20171215
Customer 24/6/20171315
Customer 25/2/20171515
Customer 26/5/20171015
Customer 37/4/20171212
Customer 38/3/20171112
Customer 39/2/20171012
Customer 310/2/2017912
Customer 311/1/2017312
Customer 312/4/2017312

Thank you in Advance!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
luxpbi Helper V
Helper V

Re: New Column to calculate MAX up to that point

Hi @mbelandria

 

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!

View solution in original post

2 REPLIES 2
Highlighted
luxpbi Helper V
Helper V

Re: New Column to calculate MAX up to that point

Hi @mbelandria

 

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!

View solution in original post

mbelandria
Frequent Visitor

Re: New Column to calculate MAX up to that point

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/...

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors