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.

     

amitchandak's avatar
amitchandak
Super User
5 years ago

K_LLI , with help from a date table

Last Day Non Continuous = CALCULATE([ltr],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

 

Last Day Non Continuous EUR = CALCULATE([Eur],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.