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:

 

CompanyPO DateItem#Unit PriceIsNETSupplier
0011/6/20211101.23TRUEABC
0023/4/20211111.45FALSEBCD
0035/8/20211121.56TRUECBE
0018/8/20211111.3FALSEDEF
0039/6/20211121.89FALSECBE

 

I need to create a matrix or table showing the Unit Price (which divided into CPG and NET) for the latest PO date in the following format:

 

Item#Supplier
 CPG PriceNET Price
   

 

CPG Price condition: IsNET = FALSE + Company <> 0001

 

NET Price condition: (IsNET = TRUE + Company <> 0001) + Company = 0001

 

Much appreciated for any help!!

 

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

7 Replies

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

    "NET Price condition: (IsNET = TRUE + Company <> 0001) + Company = 0001"

     

    This will always evaluate to false.  Please check the definition.

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

      lbendlin 

       

      The statement on my OP may not be correct. The 2 conditions for NET Price:

      1. IsNET = TRUE 

      2. Everything from Company 0001 including if IsNET = FALSE

      • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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