Running total Until Current Month

[singhv2]

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. 

POBU	Process Flow	If Following the Process Flow	If POBU Has Started Following the Process Flow	PO Create Date - Date	(Result) Total POBUs Started
A	0	No	Started	3/16/2017 0:00	0
B	0	No	Started	3/17/2017 0:00	0
C	0	No	Started	3/18/2017 0:00	0
A	1	Yes	Started	3/19/2017 0:00	1
C	1	Yes	Started	3/20/2017 0:00	2
D	1	Yes	Started	3/21/2017 0:00	3
B	1	Yes	Started	3/22/2017 0:00	4
A	1	Yes	Started	3/23/2017 0:00	4
B	1	Yes	Started	3/24/2017 0:00	4
E	1	Yes	Started	3/24/2017 0:00	5
F	0	No	Not Started	3/24/2017 0:00	5

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

[singhv2 · Accepted solution]

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]

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]

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]

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]

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]

Hi @singhv2

Could you try this formual in a measure

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

Best Regards

Maggie

[singhv2]

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

[singhv2]

Hi Maggie,

It's still not helping.