Forum Discussion

zbaker83's avatar
zbaker83
Frequent Visitor
3 years ago

Need help with DAX for comparison calculation

using DAX to find Max rev and sum BOM qty. I have four core tables UID, PN, BOM, and ICR. Then I duplicate UID, BOM, and ICR to make A and B tables. My goal is to use these to have a comparison report. However, each SO can have multiple revisions (SO + XR = SOR) and the BOM can change with each revision. I want the calculation to auto-select the latest revision when comparing (across multiple SOs not just a single SO).

 

My model is built as follows:

 

 

The end result I want is for the Max rev to only show latest rev BOM in "B" per SO minus latest rev BOM in "A" per SO. I want the math to ignore lower selected revision in the slicers. For example, in this image I want SOB-2 BOM in "B" (ignoring SOB-1 BOM) minus SOB-1 BOM in "A"

 

Here is BOM table which I duplicated to create tables BOMA and BOMB:

 

My current DAX:

 

QTYA_MAXREV =
VAR MAXREV = CALCULATE(MAX(ICRA[XR]), KEEPFILTERS(ICRA))
RETURN CALCULATE(SUM(BOMA[QTY]), FILTER(ICRA, ICRA[XR]=MAXREV))
 
QTYB_MAXREV =
VAR MAXREV = CALCULATE(MAX(ICRB[XR]), KEEPFILTERS(ICRB))
RETURN CALCULATE(SUM(BOMB[QTY]), FILTER(ICRB, ICRB[XR]=MAXREV))
 
QTYB-A_MAXREV = [QTYB_MAXREV] - [QTYA_MAXREV]

 

 

Please any help would be greatly appreciated.

Need Help

2 Replies