Forum Discussion

PaulStokes's avatar
PaulStokes
Frequent Visitor
2 years ago
Solved

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%
  • some_bih's avatar
    some_bih
    2 years ago

    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's avatar
    PaulStokes
    2 years ago

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

2 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      PaulStokes
      Frequent Visitor
      2 years ago

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