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

 

CPG = 'Table'[IsNET]=FALSE() && 'Table'[Company]<>"001"
NET = 'Table'[IsNET]=TRUE() || 'Table'[Company] = "001"

 

 

These are calculated columns - no need for measures. Note that they are complementary, so you could also say

 

NET = NOT CPG

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

lbendlin 

 

I need to grab the value of the Unit Price for the latest PO Date only. I don't think the calculated column on your post above did just that, right?

 

Much appreciated for trying to help!!

  • 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