cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors