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

"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

    • 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.