Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating column values based on date match between tables

I'm attempting to perform a column calculation that will return a value based on the last date of an instrument calibration prior to a quality control check. I have two tables, Calibration and QC, wh...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi Anonymous ,

     

    here are two calc Columns that I think do the trick

     

     

    last Cal Xq val = 
var serial = [Serial]
var calDate = [QC Date]
var maxDate = CALCULATE(MAX('Calibration'[Cal Date]), FILTER(Calibration, Calibration[Serial] = 'QC'[Serial] && Calibration[Cal Date] <= calDate ))
return 
CALCULATE(MAX('Calibration'[Cal Xq]), FILTER('Calibration', Calibration[Serial] = serial && Calibration[Cal Date] = maxDate))

     

     

     

    var = DIVIDE([QC Xq], [last Cal Xq val])-1

     

     

     

    Hope this Helps


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

richbenmintz's avatar
richbenmintz
Resident Rockstar
6 years ago

Hi Anonymous,

 

What is the difference between the RECORDS_AD and RECORDS_RP Tables? Is RECORDS_RP[Xq] a calculation that references RECORDS_AD[Xq]?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Apologies, I should have made the language consistent with the example above - RECORDS_AD is essentially the Calibration table ("AD" for calibration "adjustment"), and RECORDS_RP is the QC table (RP for "routine performance" check); so my intent is to calculate the difference between each RECORDS_RP[Xq] and the corresponding RECORDS_AD[Xq] from the last calibration adjustment date. 

 

Sorry for the confusion!

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    6 years ago

    Hi Anonymous,

     

    Can you provide a sample pbix with the error?