Showing latest date value with multiple conditions

[Helpful_Fun4848]

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	112	1.56	TRUE	CBE
001	8/8/2021	111	1.3	FALSE	DEF
003	9/6/2021	112	1.89	FALSE	CBE

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 Price	NET Price

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

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

Much appreciated for any help!!

[v-jayw-msft · Accepted solution]

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]

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

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

[Helpful_Fun4848]

@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]

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]

@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]

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

[Helpful_Fun4848]

@lbendlin 

So, this is my data:

Company	PO Date	Item#	Unit Price	IsNET	Supplier
0001	1/6/2021	110	$1.23	TRUE	ABC
0002	3/4/2021	111	$1.45	FALSE	BCD
0003	5/8/2021	112	$1.56	TRUE	CBE
0001	8/8/2021	111	$1.30	FALSE	DEF
0003	9/6/2021	112	$1.89	FALSE	CBE
0003	10/1/2021	110	$1.30	TRUE	ABC

And this is the outcome:

Supplier Name	CBE	ABC
Item#	CPG	NET	CPG	NET
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