Min & Max Value per Month on a Running Total - Performance issue

[Samkar78]

Hi,

In a cash flow report I need to show the dailly postition of the cash accounts; it's basically a runing total of all transactions.

No particular problem with that, I use this mesaure to calculate the running total :

running total = 
CALCULATE(  Sum(GL_ENTRY[Amount]),

            CALENDAR[DAY] <= MAX(CALENDAR[DAY]),

            ALL(CALENDAR[DAY])

        )

Then I need to show the min and the max value of the month for this running total. I create 2 other measures this way :

Min monthly position  = MINX ( ALL ( CALENDAR[DAY]),

        CALCULATE([running total],

        VALUES ( CALENDAR[YEAR_MONTH] ) ) )

Same for Max position.

Basically it works I have the expected result :

BUT I have a severe performance issue. The Min and max value only appear after few minutes and when filters limit the number of transactions. Otherwise I got an error message.

There are 18 Million records in my fact table GL_ENTRY.
Is there a more efficient way to show the Min and the Max monthly value ?

Thanks

[Samkar78]

I'm thinking of a the combination of a Minx with Summarize function by DAY / COMPANY / ACCOUNT / Running Total
Can it works?

[some_bih]

Hi @Samkar78 summarize is always good option, just try it intcomporate into variable as table and after that iterate this temporary table to get result (I guess measure)

[Samkar78]

I think I'm not far from the solution. My summarize table is only 37 K lines vs the 18 M lines of the fact table.

But I have trouble to combine the Minx with the Summarize function

This code works but not If I want to show the Min monthly value on a chart with a daily scale

Min Runin amount per Month =
Var TableVar =
SUMMARIZE(FILTER(GL_ENTRY, LEFT(GL_ENTRY[ACCOUNT],3)="512"),
GL_ENTRY[ACCOUNT],
GL_ENTRY[COMPANY],
GL_ENTRY[MONTH_YYYYMM],
GL_ENTRY[POSTING_DATE],
"AMTBANK", Sum(GL_ENTRY[AMOUNT]),
"RUNBANK", CALCULATE( Sum(GL_ENTRY[AMOUNT]),
ALLEXCEPT(GL_ENTRY, GL_ENTRY[COMPANY],GL_ENTRY[ACCOUNT]),
GL_ENTRY[POSTING_DATE] <= EARLIER(GL_ENTRY[POSTING_DATE])
)
)
RETURN
MINX(TableVar, [RUNBANQUE])


OK

KO

[some_bih]

Hi @Samkar78 again try with variable

Min monthly position  = 

VAR _selected_period = VALUES(CALENDAR[YEAR_MONTH])

VAR _result=

CALCULATE(

MINX (ALL(CALENDAR[DAY]),

[running total]),

_selected_period

)

RETURN _result

[Samkar78]

Unfortunately it doesn't make a significant difference to use a variable in the min monthly position 🙁

[some_bih]

Hi @Samkar78 I suggest to analyze it in "isolated scenario" meaning that you remove any other visuals in canvas / report and check timing, this way you figure out how other visuals affect your specific one. In addition, use DAX Studio to trace performance and accordingly eventually adjust code.

Another, complex maybe approach would be to "move" amounts to Calendar / Date Table if it suits your need. This way as probably your Date / Calendar table is much much lowe concerning cardinality than your Fact table. Think about it.🙂

Hope this help

[some_bih]

HI @Samkar78 try with variable for max date

running total =
VAR _max_date=MAX(CALENDAR[DAY])

CALCULATE( Sum(GL_ENTRY[Amount]),

CALENDAR[DAY] <= _max_date,
ALL(CALENDAR[DAY])
)

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

[Samkar78]

Much better indeed ! I don't have error message anymore.
But the line chart is still a bit long to show up (arround 50 sec).

Any advice to tune the Min and Max measures ?