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!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi richbenmintz,

Apologies for the slow reply! I had been pulled into other projects and this one got backburnered. 

 

I'm using your two-column solution which looks promising, but getting a circular dependency error for the second column when I attempt the DIVIDE function. 

 

Using the information found at https://community.powerbi.com/t5/Desktop/Row-Identifier-within-Power-BI-for-Desktop-Circular-dependencies/td-p/6381 I've been trying to use ALLEXCEPT in the CALCULATE function to fix the error, but haven't had much luck.

 

Thanks again for your help!

richbenmintz's avatar
richbenmintz
Resident Rockstar
6 years ago

can you share your pbix