cancel
Showing results for
Did you mean:
Highlighted
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.

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

### I have book! Learn Power BI from Packt

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Frequent Visitor

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

Hi Greg

I tried:

MaxDifference =
VAR __maxDate = MAX([Date])
RETURN
MAXX(__table, [Difference])

and also:

MaxDifference =
VAR __maxDate = MAX([Date])
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])
RETURN
MAXX(__table, [__Difference])

### I have book! Learn Power BI from Packt

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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 105 members 1,502 guests
Recent signins: