cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
singhv2
Helper I
Helper I

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.

 

So I've the below table summary from the main fact table, I've. 

 

POBUProcess FlowIf Following the Process FlowIf POBU Has Started Following the Process FlowPO Create Date - Date(Result)
Total
POBUs Started
A0NoStarted3/16/2017 0:000
B0NoStarted3/17/2017 0:000
C0NoStarted3/18/2017 0:000
A1YesStarted3/19/2017 0:001
C1YesStarted3/20/2017 0:002
D1YesStarted3/21/2017 0:003
B1YesStarted3/22/2017 0:004
A1YesStarted3/23/2017 0:004
B1YesStarted3/24/2017 0:004
E1YesStarted3/24/2017 0:005
F0NoNot Started3/24/2017 0:005

 

Here, The First Column has the Operating Units Codes,

Second Column Shows the Code of Process Flow i.e. 1 - Pass, 0 - Fail.

Column 3 Tells, if Column 2 was a Pass or not, on each date, the transaction was performed.

Column 4 tells, if the respective Operating Unit has started following the process or not .

Column 5 has the transaction date.

 

and Last Column is the result I am looking for. i.e. Total POBUs started. I need to make a running total unit chart, such that I can show the performance by each month.

 

Looking forward for someone's help.

 

Rgds,

 

Vikrant Singh

1 ACCEPTED SOLUTION
singhv2
Helper I
Helper I

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])

View solution in original post

8 REPLIES 8
singhv2
Helper I
Helper I

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
Community Support
Community Support

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])))

2.png

 

 

Best Regards

Maggie

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.

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

Hi Maggie,

 

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

Hi @singhv2

Could you try this formual in a measure

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

 

Best Regards

Maggie

Do we have any other way to find out the [New Flow] ? I am stuck with the New Flow Code.

Hi Maggie,

 

It's still not helping.

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Top Solution Authors
Users online (382)