cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
veladon Frequent Visitor
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):

 

DateMyValMaxMyValDifferenceMaxDifference
01/01/201810010000
02/01/201810110100
03/01/201810010111
04/01/201810110101
05/01/201810210201
06/01/201810110211
07/01/201810010222

 

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
v-yulgu-msft Super Contributor
Super Contributor

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

Hi @veladon,

 

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

1.PNG

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
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!


veladon Frequent Visitor
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? Smiley Happy

 

Thanks again,

James

Super User
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!


veladon Frequent Visitor
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
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!


v-yulgu-msft Super Contributor
Super Contributor

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

Hi @veladon,

 

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

1.PNG

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 268 members 3,011 guests
Please welcome our newest community members: