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