Forum Discussion

jaymetbar's avatar
jaymetbar
Frequent Visitor
6 years ago

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...
Need Help
jaymetbar's avatar
jaymetbar
Frequent Visitor
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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_hTYt3FNoMlzT7tmj2QBxFxH5DmebvYe5v9P8hrpIA?e=ip6cXD

 

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.