Forum Discussion
Need help creating calculated column using data from different table between 2 dates
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!
7 Replies
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
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.
jaymetbar
Did you missed out a column somewhere that should show us NorthAmps/West Amps for each cell?Regards
Paul
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.
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)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.
