cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX rolling Max of differences between value and max value over time

Hi

I am trying use DAX in Power BI to calculate the Max(Difference) column in the table below, and for it to be usable on a line graph with a date slicer (so it can recalculate based upon the date filter applied):

 Date MyVal MaxMyVal Difference MaxDifference 01/01/2018 100 100 0 0 02/01/2018 101 101 0 0 03/01/2018 100 101 1 1 04/01/2018 101 101 0 1 05/01/2018 102 102 0 1 06/01/2018 101 102 1 1 07/01/2018 100 102 2 2

So far I have (all formulas below are defined as measures):

MaxMyVal := CALCULATE(
MAX(MyTable[MyVal]),
FILTER(
ALLSELECTED(MyTable[Date]),
MyTable[Date] <= MAX(MyTable[Date])
)
)

Difference := MyTable[MaxMyVal] - SUM(MyTable[MyVal])

MaxDifference = MAXX(MyTable, [Difference])

However MaxDifference gives me the error "A circular dependency was detected: MyTable[MaxDifference]".

I have tried following the article here - https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ - however I must not be understanding properly as I can't get this to work while also respecting the date slicer.

Thanks for all your help,

James

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: DAX rolling Max of differences between value and max value over time

```MaxDifference =
MAXX (
FILTER ( ALLSELECTED ( Mytable[Date] ), MyTable[Date] <= MAX ( MyTable[Date] ) ),
[Difference]
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User

## Re: DAX rolling Max of differences between value and max value over time

See if these measures work:

```MaxMyVal =
VAR __maxDate = MAX([Date])
VAR __table = FILTER(ALL(Table8),[Date]<=__maxDate)
RETURN
MAXX(__table,[MyVal])

Difference = Table8[MaxMyVal] - SUM(Table8[MyVal])

MaxDifference =
VAR __maxDate = MAX([Date])
VAR __table = SUMMARIZE(FILTER(ALL(Table8),[Date]<=__maxDate),[Date],[MyVal],"__MaxMyVal",[MaxMyVal],"__Difference",[Difference])
RETURN
MAXX(__table, [Difference])```

Attaching PBIX, you want Table8 and Page 3

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: DAX rolling Max of differences between value and max value over time

Hi Greg

Many thanks for the super quick reply. Your answer seems to work, however when increasing the number of rows, the SUMMARIZE function seems to get exponentially slower:

0.5yrs = 1sec

1yr (i.e. 365 rows) = 3s

1.5yrs = 9s

2yrs (730 rows) = 20s

Reading this ( https://www.sqlbi.com/articles/all-the-secrets-of-summarize/ ) I assume this function is using a cross join which is causing this behaviour.

I need this to work for roughly 5 years worth of data, so about 1500 rows. Any other ideas?

Thanks again,

James

Super User

## Re: DAX rolling Max of differences between value and max value over time

Huh, I actually discussed that when I was live streaming answering that question. Try replacing SUMMARIZE with ADDCOLUMNS

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: DAX rolling Max of differences between value and max value over time

Hi Greg

Using ADDCOLUMNS instead of SUMMARIZE took virtually the same time.

I tried:

MaxDifference =
VAR __maxDate = MAX([Date])
VAR __table = ADDCOLUMNS(FILTER(ALL(MyTable),[Date]<=__maxDate),"__MaxMyVal",[MaxMyVal],"__Difference",[Difference])
RETURN
MAXX(__table, [Difference])

and also:

MaxDifference =
VAR __maxDate = MAX([Date])
VAR __table = ADDCOLUMNS(FILTER(ALL(MyTable),[Date]<=__maxDate),"__MaxMyVal",[MaxMyVal],"__Difference",[Difference])
RETURN
MAXX(__table, [__Difference])

but they both took ~20seconds for 2 years of data rows.

Thanks

James

Super User

## Re: DAX rolling Max of differences between value and max value over time

Thinking about this, I would take out MyMaxValue individually in the summarized table, because that is just adding additional, unnecessary calculations because Difference also calculates that value independently.

MaxDifference =
VAR __maxDate = MAX([Date])
VAR __table = ADDCOLUMNS(FILTER(ALL(MyTable),[Date]<=__maxDate),"__Difference",[Difference])
RETURN
MAXX(__table, [__Difference])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super Contributor

## Re: DAX rolling Max of differences between value and max value over time

```MaxDifference =
MAXX (
FILTER ( ALLSELECTED ( Mytable[Date] ), MyTable[Date] <= MAX ( MyTable[Date] ) ),
[Difference]
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 268 members 3,011 guests
Recent signins: