cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

 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

1 ACCEPTED SOLUTION
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])

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

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

Best Regards

Maggie

Helper I

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.

Community Support

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

Helper I

Hi Maggie,

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

Community Support

Hi @singhv2

Could you try this formual in a measure

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

Best Regards

Maggie

Helper I

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

Helper I

Hi Maggie,

It's still not helping.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors