cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## 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?

 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

1 ACCEPTED SOLUTION
Helper V

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!

2 REPLIES 2
Anonymous
Not applicable

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

Helper V

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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors