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

[veladon]

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

[v-yulgu-msft · Accepted solution]

Hi @veladon,

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

Best regards,

Yuliana Gu

[Greg_Deckler]

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

[veladon]

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

[Greg_Deckler]

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

[veladon]

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

[Greg_Deckler]

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])