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 averag...
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.
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.