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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
K_LLI
Frequent Visitor

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 transaction dates. My goal is on right side where I have marked with colours the results which I would like to get ("Previous Ltr" and "Previos EUR"). I have spent so many hours trying to get this working so it would so great if someone could help me?

 

In addtition this case linked to quite big dataset so I would like to know if it is better to create calculated columns for this? Or are we able to solve this with measure only?

 

Many thanks for your support in advance!

 

 

Transactions sample.PNG

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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 )
    )

 

 100.png

 

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.

 

View solution in original post

5 REPLIES 5
v-easonf-msft
Community Support
Community Support

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 )
    )

 

 100.png

 

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.

 

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):

KevinA_1-1727844567357.png

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

K_LLI
Frequent Visitor

Hi,

 

I added date from transactions table and date from Date table to report and now it is shifting the calculation to next day (1.5.2020 -> 2.5.2020). The calculation should be shifted to next transaction date (1.5.2020 -> 4.5.2020) as shown in my first post. Can anyone help?

 

I have joined my calendar table to this transaction table and marked is as date table.

 

Thanks for the support @amitchandak!  do you find any solutions to this? 

 

 

 

Report transactions.PNG

K_LLI
Frequent Visitor

Thank you for your support! Regardin this part: 

 
MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
 
Should this refer to transactions table? Do I create this as measure or calculated column?
amitchandak
Super User
Super User

@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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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