Forum Discussion

Helpful_Fun4848's avatar
Helpful_Fun4848
Icon for Helper III rankHelper III
4 years ago
Solved

Showing latest date value with multiple conditions

Hi all, I have the following set of data:   Company PO Date Item# Unit Price IsNET Supplier 001 1/6/2021 110 1.23 TRUE ABC 002 3/4/2021 111 1.45 FALSE BCD 003 5/8/2021 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Helpful_Fun4848 ,

     

    CPG = 
var _latest = CALCULATE(MAX('Table'[PO Date]),FILTER('Table','Table'[IsNET]="FALSE"&&'Table'[Company]<>"0001"))
return
CALCULATE(SUM('Table'[Unit Price]),FILTER('Table','Table'[PO Date]=_latest))
    NET = 
var _latest = CALCULATE(MAX('Table'[PO Date]),FILTER('Table',('Table'[IsNET]="TRUE"&&'Table'[Company]<>"0001")||'Table'[Company]="0001"))
return
CALCULATE(SUM('Table'[Unit Price]),FILTER('Table','Table'[PO Date]=_latest))

     

    Best Regards,

    Jay

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Based on your sample data, what would be the expected outcome?  I'm still not clear on your process logic.

Helpful_Fun4848's avatar
Helpful_Fun4848
Icon for Helper III rankHelper III
4 years ago

lbendlin 

 

So, this is my data:

 

CompanyPO DateItem#Unit PriceIsNETSupplier
00011/6/2021110$1.23TRUEABC
00023/4/2021111$1.45FALSEBCD
00035/8/2021112$1.56TRUECBE
00018/8/2021111$1.30FALSEDEF
00039/6/2021112$1.89FALSECBE
000310/1/2021110$1.30TRUEABC

 

And this is the outcome:

Supplier NameCBEABC
Item#CPGNETCPGNET
112$1.89$1.56  
110   $1.30

 

Price on the outcome table is Unit Price from the latest PO Date for the respective supplier.

 

If I can summarize all the conditions:

1. Both CPG and NET =  Unit Price from the latest PO Date

2. CPG: IsNET = FALSE

3. NET: IsNET = TRUE

4. Company 0001 = NET (even if IsNET = FALSE)

 

The table outcome is not correct after I pasting it here. 

It should be:

     CBE            ABC

CPG NET    CPG NET