DAX - POWER PIVOT measures using date before date

[PaulStokes]

Hello fellow DAX enthusiasts,

Help needed please. 

I can do this simply in Ms Excel. But I am struggling to write the DAX to have as measures in a Power Pivot in Excel.

Sample data below drawn from Table named PriceData. PriceData data-model is joined to Tbl_Dates (calendar 2023) 

Please advise how I can write a measure to get EarlyDate and EarlyValue. 

Thanks in advance

PART	SUP_NAME	DATE		Cost		EarlyDate		EarlyValue		% Change
ABC	Test123	22/06/2023		1.5044
ABC	Test123	21/07/2023		1.5044		22/06/2023		1.5044		0.0%
ABC	Test123	29/08/2023		1.5044		21/07/2023		1.5044		0.0%
ABC	Test123	18/09/2023		3.625		29/08/2023		1.5044		141.0%
ABC	Test123	03/10/2023		1.5088		18/09/2023		3.625		-58.4%
ABC	Test123	24/10/2023		1.6244		03/10/2023		1.5088		7.7%

[some_bih · Accepted solution]

Hi @PaulStokes possible solution as following

create 3 different calculated columns as you are calculating amounts based on row level

Creation order is important to avoid complexity. Output below is the same as your picture

1. EarlyDate

=
VAR __part=MyTable[PART]
VAR __sup_name=MyTable[SUP_NAME]
VAR __date=MyTable[DATE]
RETURN
CALCULATE(
MAX(MyTable[DATE]);
FILTER(MyTable;MyTable[PART]=__part && MyTable[SUP_NAME]=__sup_name && MyTable[DATE]<__date)
)

2. EarlyValue

=VAR __part=MyTable[PART]
VAR __sup_name=MyTable[SUP_NAME]
VAR __date_early=MyTable[EarlyDate]
RETURN
CALCULATE(
SUM(MyTable[Cost]);
FILTER(MyTable;MyTable[PART]=__part && MyTable[SUP_NAME]=__sup_name && MyTable[DATE]=__date_early)
)

3.% Change

=
IF(MyTable[EarlyValue]=BLANK();BLANK();
MyTable[Cost]/MyTable[EarlyValue]-1)

[some_bih · Accepted solution]

Hi @PaulStokes possible solution as following

create 3 different calculated columns as you are calculating amounts based on row level

Creation order is important to avoid complexity. Output below is the same as your picture

1. EarlyDate

=
VAR __part=MyTable[PART]
VAR __sup_name=MyTable[SUP_NAME]
VAR __date=MyTable[DATE]
RETURN
CALCULATE(
MAX(MyTable[DATE]);
FILTER(MyTable;MyTable[PART]=__part && MyTable[SUP_NAME]=__sup_name && MyTable[DATE]<__date)
)

2. EarlyValue

=VAR __part=MyTable[PART]
VAR __sup_name=MyTable[SUP_NAME]
VAR __date_early=MyTable[EarlyDate]
RETURN
CALCULATE(
SUM(MyTable[Cost]);
FILTER(MyTable;MyTable[PART]=__part && MyTable[SUP_NAME]=__sup_name && MyTable[DATE]=__date_early)
)

3.% Change

=
IF(MyTable[EarlyValue]=BLANK();BLANK();
MyTable[Cost]/MyTable[EarlyValue]-1)

[PaulStokes · Accepted solution]

WOW ! BIG Kudos to some_bih for the reply and answer to my question. Resolved 🙂