Forum Discussion

K_LLI's avatar
K_LLI
Frequent Visitor
5 years ago
Solved

Values from previous transaction date

Hi,   I am trying to create DAX measures to get values from previous transaction dates. On left side is Transactions table where is should be able to calculate LTR and EUR values from previous tran...
Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, K_LLI 

    I prefer to  use calculated column as below:

     

    C_Prvevious Eur =
CALCULATE (
    MAX ( Transactions[Eur] ),
    Transactions,
    Transactions[Date] < EARLIER ( Transactions[Date] ),
    Transactions[Project] = EARLIER ( Transactions[Project] )
)

    C_Prvevious Ltr =
CALCULATE (
    MAX ( Transactions[Ltr] ),
    Transactions,
    Transactions[Date] < EARLIER ( Transactions[Date] ),
    Transactions[Project] = EARLIER ( Transactions[Project] )
)

     

    You  can also try measure as below:

     

    M_Prvevious Eur = 
VAR previousdate =
    CALCULATE (
        MAX ( Transactions[Date] ),
        FILTER (
            ALLEXCEPT ( Transactions, Transactions[Project] ),
            Transactions[Date] < MAX ( Transactions[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Transactions[Eur] ),
        FILTER ( ALLEXCEPT(  Transactions,Transactions[Project]), Transactions[Date] = previousdate )
    )
    M_Prvevious Ltr = 
VAR previousdate =
    CALCULATE (
        MAX ( Transactions[Date] ),
        FILTER (
            ALLEXCEPT ( Transactions, Transactions[Project] ),
            Transactions[Date] < MAX ( Transactions[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Transactions[Ltr] ),
        FILTER ( ALLEXCEPT(  Transactions,Transactions[Project]), Transactions[Date] = previousdate )
    )

     

     

     

    Please check my attached pbix file for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-easonf-msft's avatar
v-easonf-msft
Community Support
5 years ago

Hi, K_LLI 

I prefer to  use calculated column as below:

 

C_Prvevious Eur =
CALCULATE (
    MAX ( Transactions[Eur] ),
    Transactions,
    Transactions[Date] < EARLIER ( Transactions[Date] ),
    Transactions[Project] = EARLIER ( Transactions[Project] )
)

C_Prvevious Ltr =
CALCULATE (
    MAX ( Transactions[Ltr] ),
    Transactions,
    Transactions[Date] < EARLIER ( Transactions[Date] ),
    Transactions[Project] = EARLIER ( Transactions[Project] )
)

 

You  can also try measure as below:

 

M_Prvevious Eur = 
VAR previousdate =
    CALCULATE (
        MAX ( Transactions[Date] ),
        FILTER (
            ALLEXCEPT ( Transactions, Transactions[Project] ),
            Transactions[Date] < MAX ( Transactions[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Transactions[Eur] ),
        FILTER ( ALLEXCEPT(  Transactions,Transactions[Project]), Transactions[Date] = previousdate )
    )
M_Prvevious Ltr = 
VAR previousdate =
    CALCULATE (
        MAX ( Transactions[Date] ),
        FILTER (
            ALLEXCEPT ( Transactions, Transactions[Project] ),
            Transactions[Date] < MAX ( Transactions[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Transactions[Ltr] ),
        FILTER ( ALLEXCEPT(  Transactions,Transactions[Project]), Transactions[Date] = previousdate )
    )

 

 

 

Please check my attached pbix file for more details.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Kevin-A's avatar
Kevin-A
New Member
1 year ago

Hello, I am attempting to use this approach as an intermediary step in decumulating a column, but I'm having an issue when the max value doesn't occur at the max date for a product (since my data moves up and down), for example, when I translated this logic into my table I get results that look like this (faticious example):

As you can see, I am getting the max value repeating once that value is hit.  I would like to rather see the true previous value so I can simply create an incremental transaction that is the difference of the 2.

 

thank you for you help!

Kevin