Forum Discussion

singhv2's avatar
singhv2
Helper I
8 years ago
Solved

Running total Until Current Month

Hi There,   I'll really appreciate somone's urgent support.   I've gone through multiple Running Total examples, however, I am not sure if it's helping me out. I'll appreciate someone's help.   ...
Need Help
Show and Tell
Tips and Tricks
  • singhv2's avatar
    singhv2
    8 years ago

    I've came across another method.

     

    Get the first date of the POBU with first True case. i.e. Process Flow = 1

     

    First Date = CALCULATE

                                         (MIN(Table[PO Create Date - Date].[Date]),

                                         FILTER(ALL('Standard Naming Detail - All - Metrics - V1'),Table[POBU]=EARLIER('Table'[POBU]) && Table [Process Flow]="1")

     

    Once the First Date Column is created, get the Cummulative Total using the below DAX:

     

    Pareto Total Per Month =
    VAR
    CurrentTotal = 'Table'[First Date]
    Return
    SUMX(FILTER('Table','Table'[First Date]<=CurrentTotal),'Table'[Process Flow])

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi singhv2

Create an index column in query editor from 1

Create two calculated columns

new flow =
VAR flag =
    IF (
        [Process Flow] = 0,
        BLANK (),
        CALCULATE (
            SUM ( 'table'[Process Flow] ),
            FILTER ( ALLEXCEPT ( 'table', 'table'[POBU] ), [Index] <= EARLIER ( [Index] ) )
        )
    )
RETURN
    IF ( flag > [Process Flow], 0, [Process Flow] )
total = CALCULATE(SUM([new flow]),FILTER(ALL('table'),[Index]<=EARLIER([Index])))

 

 

Best Regards

Maggie

singhv2's avatar
singhv2
Helper I
8 years ago

Thanks Maggie.

 

I tried using the DAX, however, it's giving me Insufficient memory Error. I am running this query on 1 Million rows, in a 8 GB Laptop.

 

Can you help me to fix this issue.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi singhv2

    How about running these query respectively

    flag =
    IF (
        [Process Flow] = 0,
        BLANK (),
        CALCULATE (
            SUM ( 'table'[Process Flow] ),
            FILTER ( ALLEXCEPT ( 'table', 'table'[POBU] ), [Index] <= EARLIER ( [Index] ) )
        )
    )

    new flow =
IF ( flag > [Process Flow], 0, [Process Flow] )

    total = CALCULATE(SUM([new flow]),FILTER(ALL('table'),[Index]<=EARLIER([Index])))

    Best regards

    Maggie

    • singhv2's avatar
      singhv2
      Helper I
      8 years ago

      Hi Maggie,

       

      It's still not helping.

    • singhv2's avatar
      singhv2
      Helper I
      8 years ago

      Hi Maggie,

       

      Can you think of some alternate way.... Index <= Earlier(Index) is taking too much time to execute 1 Million rows.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        Hi singhv2

        Could you try this formual in a measure

        total = CALCULATE(SUM([new flow]),FILTER(ALL('table'),[Index]<=MAX([Index])))

         

        Best Regards

        Maggie