Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PaulStokes
Frequent Visitor

DAX - POWER PIVOT measures using date before date

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

 

PARTSUP_NAMEDATE Cost EarlyDate EarlyValue % Change
ABCTest12322/06/2023 1.5044      
ABCTest12321/07/2023 1.5044 22/06/2023 1.5044 0.0%
ABCTest12329/08/2023 1.5044 21/07/2023 1.5044 0.0%
ABCTest12318/09/2023 3.625 29/08/2023 1.5044 141.0%
ABCTest12303/10/2023 1.5088 18/09/2023 3.625 -58.4%
ABCTest12324/10/2023 1.6244 03/10/2023 1.5088 7.7%
2 ACCEPTED SOLUTIONS
some_bih
Super User
Super User

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_0-1708439135778.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

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

View solution in original post

2 REPLIES 2
some_bih
Super User
Super User

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_0-1708439135778.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors