 Find the date based on running total • Question

• Hi

I have to find when an unit reach a specific volume of production.  To do it I believe that I need the running total, compare the running total to the production volume expected and get the according date.

Sample Data :  Want to kwon when each unit will reach 1500 pieces

Unit 1  2010-01-01  500 pieces      Running total 500

Unit 1 2010-01-02   600 pieces      Running total 1100

Unit 1 2010-01-03  450 pieces      Running total 1550

Unit 2  2010-01-01  800 pieces    Running total 800

Unit 2 2010-01-02   900 pieces    Running total 1700

So Unit 1 date is 2010-01-03 while Unit 2 is 2010-01-02

In the real life,  production may take more than a year to reach the desired level so I cannot use TOTALYTD and there is more than one row per date.

DanielP38
Wednesday, May 4, 2011 6:04 PM

• Daniel

Try next formulas. You probably will run into the same problem but it is worth trying.

What you are doing is correct and 4GB should be sufficient.

Output Cumul: =CALCULATE(sum(Production[Output]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Production Date])>=Production[Production Date]))

Previous Date: =CALCULATE(Max(Production[Production Date]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Production Date])>Production[Production Date]))

Previous Output Cumul: =CALCULATE(Values(Production[Output Cumul]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Previous Date])=Production[Production Date]))

Max Value: =CALCULATE(VALUES(Parameter[value]),FILTER(Parameter,Parameter[item]="Max value"))

Level Reached Date: =IF(AND(Production[Output Cumul]>Production[Max Value],Production[Previous Output Cumul]<Production[Max Value]),Production[Production Date],BLANK())

I created a linked table Parameter to store the maximum level value.

 item value Max Value 1500

Check also next article of Kasper de Jonge. It is about where to put the most discriminative conditions.

http://www.powerpivotblog.nl/tune-your-powerpivot-dax-query-dont-use-the-entire-table-in-a-filter-and-replace-sumx-if-possible

Eddy N.
• Edited by Friday, May 6, 2011 10:01 AM added some text
• Marked as answer by Friday, May 6, 2011 2:14 PM
Friday, May 6, 2011 9:58 AM

All replies

• This is an Excel question, not a PowerPivot question. Further, you are not supplying enough information about the processes to calculate an answer.
Wednesday, May 4, 2011 10:24 PM
• Daniel

It is may be not PowerPivot related but next can be an answer to your question.

in column D as from row 3:  =SUMIF(A\$3:A3;A3;C\$3:C3)

in column E as from row 3:   =IF(AND(\$D\$2<=D5;E4="";D4<\$D\$2);B5;"")

Condition: data is sorted by unit and by date

 1 A B C D E 2 Max value: 1500 3 Unit 1 1/01/2010 500 500 4 Unit 1 2/01/2010 600 1100 5 Unit 1 3/01/2010 450 1550 3/01/2010 5 Unit 1 4/01/2010 350 1900 6 Unit 1 5/01/2010 400 2300 7 Unit 2 1/01/2010 800 800 8 Unit 2 2/01/2010 900 1700 2/01/2010 9 Unit 2 3/01/2010 250 1950

The problem could also be solved in PowerPivot with DAX.

Eddy N.

• Edited by Thursday, May 5, 2011 10:47 AM added extra text
Thursday, May 5, 2011 10:45 AM
• Thanks Eddy N and David

The example provided is only a simplified version of the problem.

To understand why I want to use DAX to solve the problem, here are some consideration.

1. The raw data used covers over 20 years of production.

2. There is over 4000 units, that I want to group by familly

3. Each unit will produce at different rate.  Some once a week, other twice while some only every 2 weeks.

4. Each unit have different starting and endig date.  None will be in use during the whole 20 years.

5. The total of rows in the raw table is over 500 000.

I have try to use DAX calculate with several Filter and All combinaison without any succes.

DanielP38
Thursday, May 5, 2011 12:53 PM
• I find a nearly working solution ...

1.  Add a calculated column in the PowerPivot window to get the cumulated total

[Output Cumul] =CALCULATE(sum(Production[Output]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Production Date])>=Production[Production Date]))

2. Add another calculated column in the same window to get when the level is reach

[Level reached date] =CALCULATE(Min(Production[Production Date]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && Production[Output Cumul] > 22000))

I was able to get it working using a small sample of data (around 15000 rows) but when I try it in the full set of data (500 000 rows) I keep having a memory error/too many error warning.

Can you tell me what I'm doing wrong ?

The computer is running Windows 7 64 bits and i7 core CPU and 4 gigs of ram.

DanielP38
Thursday, May 5, 2011 6:17 PM
• Daniel

Try next formulas. You probably will run into the same problem but it is worth trying.

What you are doing is correct and 4GB should be sufficient.

Output Cumul: =CALCULATE(sum(Production[Output]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Production Date])>=Production[Production Date]))

Previous Date: =CALCULATE(Max(Production[Production Date]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Production Date])>Production[Production Date]))

Previous Output Cumul: =CALCULATE(Values(Production[Output Cumul]),FILTER(Production, EARLIER(Production[Unit])=Production[Unit] && EARLIER(Production[Previous Date])=Production[Production Date]))

Max Value: =CALCULATE(VALUES(Parameter[value]),FILTER(Parameter,Parameter[item]="Max value"))

Level Reached Date: =IF(AND(Production[Output Cumul]>Production[Max Value],Production[Previous Output Cumul]<Production[Max Value]),Production[Production Date],BLANK())

I created a linked table Parameter to store the maximum level value.

 item value Max Value 1500

Check also next article of Kasper de Jonge. It is about where to put the most discriminative conditions.

http://www.powerpivotblog.nl/tune-your-powerpivot-dax-query-dont-use-the-entire-table-in-a-filter-and-replace-sumx-if-possible

Eddy N.
• Edited by Friday, May 6, 2011 10:01 AM added some text
• Marked as answer by Friday, May 6, 2011 2:14 PM
Friday, May 6, 2011 9:58 AM
• Thank you very much Eddy

Your solution is great, I like the parameter table idea, it's exacly what I need

DanielP38
Friday, May 6, 2011 2:14 PM