Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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

 

8 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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 ?

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • some_bih's avatar
        some_bih
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • some_bih's avatar
      some_bih
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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)

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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