Forum Discussion
Need help creating calculated column using data from different table between 2 dates
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.
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.
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.