Need help creating calculated column using data from different table between 2 dates

[jaymetbar]

Hello Again,

I have a table titled "Daily Amperage Data" and I have a table titled "Current Efficiency". I would like to add a new column within "Current Efficiency" which will generate an average amperage for the time between when a cell is harvested. I would also like to add another column which would have the value of the number of days that have elapsed since that cell was harvested.

Here are my Tables:

Daily Amperage Data

Production Date	North Amps	West Amps
Friday, January 1, 2010	23605	21511
Saturday, January 2, 2010	23397	20766
Sunday, January 3, 2010	20862	24511
Monday, January 4, 2010	24864	23175
Tuesday, January 5, 2010	22533	20375
Wednesday, January 6, 2010	23552	23112
Thursday, January 7, 2010	20299	20081
Friday, January 8, 2010	22345	20276
Saturday, January 9, 2010	23669	23203
Sunday, January 10, 2010	24647	22383
Monday, January 11, 2010	20405	21362
Tuesday, January 12, 2010	22945	21624
Wednesday, January 13, 2010	20443	22062
Thursday, January 14, 2010	24439	24136
Friday, January 15, 2010	20382	21179
Saturday, January 16, 2010	20456	22797
Sunday, January 17, 2010	20032	24161

Current Efficiency 

Production Date	Cell Number	Production	True Production
Friday, January 1, 2010	150	12513	12713
Friday, January 1, 2010	160	10148	10348
Friday, January 1, 2010	170	10788	10988
Friday, January 1, 2010	250	10155	10355
Friday, January 1, 2010	260	12804	13004
Friday, January 1, 2010	270	11242	11442
Saturday, January 2, 2010	180	11614	11814
Saturday, January 2, 2010	190	10586	10786
Saturday, January 2, 2010	100	12399	12599
Saturday, January 2, 2010	280	12200	12400
Saturday, January 2, 2010	290	11375	11575
Saturday, January 2, 2010	200	10799	10999
Sunday, January 3, 2010	110	11413	11613
Sunday, January 3, 2010	120	12367	12567
Sunday, January 3, 2010	130	11109	11309
Sunday, January 3, 2010	210	11810	12010
Sunday, January 3, 2010	220	11941	12141
Sunday, January 3, 2010	230	11690	11890
Monday, January 4, 2010	140	11880	12080
Monday, January 4, 2010	150	12201	12401
Monday, January 4, 2010	160	12424	12624
Monday, January 4, 2010	170	10342	10542
Monday, January 4, 2010	250	11597	11797
Monday, January 4, 2010	260	11812	12012
Tuesday, January 5, 2010	270	11429	11629
Tuesday, January 5, 2010	180	11861	12061
Tuesday, January 5, 2010	190	11871	12071
Tuesday, January 5, 2010	100	12422	12622
Tuesday, January 5, 2010	280	10376	10576
Tuesday, January 5, 2010	290	12728	12928
Wednesday, January 6, 2010	200	12881	13081
Wednesday, January 6, 2010	110	10421	10621
Wednesday, January 6, 2010	120	10813	11013
Wednesday, January 6, 2010	130	11886	12086
Wednesday, January 6, 2010	210	11324	11524
Wednesday, January 6, 2010	220	11670	11870
Thursday, January 7, 2010	230	11652	11852
Thursday, January 7, 2010	140	11107	11307
Thursday, January 7, 2010	150	11745	11945
Thursday, January 7, 2010	230	10761	10961
Thursday, January 7, 2010	240	10514	10714
Thursday, January 7, 2010	250	10068	10268
Friday, January 8, 2010	160	11225	11425
Friday, January 8, 2010	170	10276	10476
Friday, January 8, 2010	180	10885	11085
Friday, January 8, 2010	260	11726	11926
Friday, January 8, 2010	270	12417	12617
Friday, January 8, 2010	280	11969	12169

So I'd like to create a new column which shows the average Amperage for cell 150 over its cycle. So initially it is harvested on January 1st. And it is harvested again on January 5th. I would like the column to be the average of amps between January 1st and January 5th. The second column should be something along the lines of Days between when that cell was harvested. 

Cells fed by the North Amperage are the "100" cells. 110, 120, 130 etc.

Cells fed by the West Amperage are the "200" cells. 210, 220, 230 etc.

Please let me know if you need further clarification. I understand I'll have null data in the first few days until the cycle repeats itself.

Thank You for your help!

[V-pazhen-msft]

@jaymetbar 

Did you missed out a column somewhere that should show us NorthAmps/West Amps for each cell?

Regards

Paul

[jaymetbar]

Hi Paul,

Sorry, i got a little hasty when making my dummy data and left out some key info. Cells that are 100-190 are fed by North Amps and cells that are 200-290 are West Amps.

[V-pazhen-msft]

@jaymetbar 

1. I assumed you related the two tables with Dates, you can move the Amps to the Current Efficiency table using, I used SWTICH instead of IF because there are may be 300, 400... cells.

Amps = SWITCH(TRUE(),
[Cell Number]>=100 && [Cell Number]<=190,RELATED('Daily Amperage Data'[North Amps]),
[Cell Number]>=200 && [Cell Number]<=290,RELATED('Daily Amperage Data'[West Amps]))

 2. Create the two expected columns with: 

Column 1 = 
CALCULATE(AVERAGE('Current Efficiency'[Amps]),FILTER('Current Efficiency',[Cell Number]=EARLIER([Cell Number])&&[Production Date]<=EARLIER([Production Date])))

Column 2 = 
var nextdate = CALCULATE(MAX([Production Date]),FILTER('Current Efficiency',[Cell Number]=EARLIER([Cell Number])&&[Production Date]<EARLIER([Production Date])))
Return DATEDIFF(nextdate,[Production Date],DAY)

Pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/ERRN_hTYt3FNoMlzT7tm...

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jaymetbar]

Or we could eliminate the need for the plating days and use a sum function to sum the amps daily between those two harvest dates. That might make it an easier calculation. I'll try that in the mean time and study more up on Earlier, Filter, expressions.

[jaymetbar]

Hi Paul!

Sorry I've been swamped in other projects and am barely circling back to this one.

So I inputted your code and it is calculating. The Plating Days calculation works fine ("Column 2" Thanks for that!)

So let me give some more detail:

We have cells that are plating every day and are fed amperages which fluctuate daily. The daily average amps is captured in the Daily Amps Table. I think the logic of sorting amps works for the "Amps" column you created. However i think the value itself is unnecessary.

I think what we want to do is have logic which looks at if a cell is in the west and the north, then it needs to take the average amperage from the "Daily Amperage Data" table between the dates it was harvested and when it was harvested last. 

I think it was trying to do it within the current efficiency table and getting a lower value.

So for cell 150 it was initially harvested on Jan 1 2010 and then it begins plating again on that date until it is harvested again on Jan 4 2010.

The Daily Amps for the North during that range were 23605, 23397, 20862, and 24864 respectively so the average amps for cell 150 over the cycle should be 23,182.

Does that make sense? I really appreciate your help and taking the time to look into this.

[Greg_Deckler]

So what tells us Cell 50 is in North amps?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[jaymetbar]

Hi Greg,

Sorry about that. I corrected the data to include more cells. Anything in the 100 series would be a North Amperage fed cell and anything in a 200 series would be a West Amperage fed cell.