topic Re: Min & Max Value per Month on a Running Total - Performance issue in DAX Commands and Tips

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

Anonymous — Thu, 13 Jul 2023 09:39:15 GMT

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

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

some_bih — Thu, 13 Jul 2023 09:46:40 GMT

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

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

Anonymous — Thu, 13 Jul 2023 10:17:28 GMT

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 ?

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

some_bih — Thu, 13 Jul 2023 11:19:10 GMT

Hi Anonymous 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

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

Anonymous — Thu, 13 Jul 2023 12:26:38 GMT

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

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

some_bih — Thu, 13 Jul 2023 12:58:55 GMT

Hi Anonymous 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

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

Anonymous — Wed, 19 Jul 2023 07:08:56 GMT

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

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

some_bih — Wed, 19 Jul 2023 08:00:54 GMT

Hi Anonymous 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)

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

Anonymous — Wed, 19 Jul 2023 13:32:04 GMT

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