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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

harshnathani

Getting Previous Values in Power BI - Part 1

 

Harsh is a Sales Manager at XYZ Corporation. While looking at the Sales Report, he wanted to check the numbers of days between 2 orders from a customer. He also wants to know the previous order (Order Date, Order No, Sales Amount) details in the same row.

While this may seem a very easy solution in Power BI, it is also one of the most frequently asked questions in the official Power BI community.

 

So, let’s try and solve this using 2 approaches.

 

Sample Table_Upload.jpg

 

 

Order Date (Date/Time), Customer Id (Text), Sales Order No (Text), Sales Amount (Numeric).

As soon as an order is placed, a Sales Order No gets generated.

 

CALCULATED COLUMNS

 

The idea here is to get the previous Sales Order Date, Sales Order Number and Sales Amount aligned to next purchase of the customer.

 

Shift_Dates_Below.JPG

 

You can use the below formula to do so

 

Previous Order Date =

CALCULATE (

    MAX ( 'Table'[Order Date] ),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[Customer ID]

        ),

        'Table'[Order Date]

            < EARLIER ( 'Table'[Order Date] )

    )

)

 

 

Similarly, since the Sales Order No is generated sequentially, you can use the similar formula.

 

Previous Sales Order No =

CALCULATE (

    MAX ( 'Table'[Sales Order Number] ),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[Customer ID]

        ),

        'Table'[Order Date]

            < EARLIER ( 'Table'[Order Date] )

    )

)

 

Shift_SalesOrderNo_Below.JPG

 

Since Dates and Sales Order No are sequential hence it was easy to shift the dates. The Max (Sales Order No) of the filtered table always gave you the previous value of the Sales Order No.

 

For Example,

Suppose the row marked in Black is the current row which is under evaluation, the table above (marked in green) gets filtered due to the value in the filter context ('Table'[Order Date] < EARLIER (Table [Order Date]) and the value returned is MAX(Order Date) which is 29-Jan-20 1:30:00 PM in this case.

 

3.JPG

 

But the Sales Amount is not sequential value. If we were to follow the same step as above, the Previous Sales Amount will return $1,192.04 while the correct value which should be returned is $200.

 

To overcome this, one way is to create an Index Column. We can Power Query and create one. But here is a method, in case you want to do this through a Calculated Column.

 

You can use the RankX function to create an index Column.

Rank Date =

VAR _rankSalesOrder =

    RANKX (

        FILTER (

            ALL (

                'Table'[Order Date],

                'Table'[Customer ID],

                'Table'[Sales Order Number]

            ),

            'Table'[Customer ID]

                = EARLIER ( 'Table'[Customer ID] )

        ),

        'Table'[Order Date],

        ,

        ASC

    )

RETURN

    _rankSalesOrder

 

  Rank_Date.JPG

 

The formula ranks the customer id based on Order Date.

 

With the help of this column we can now, find the previous Sales Amount.

Previous Sales Amount =

CALCULATE (

    MAX('Table'[Sales Amount]),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[Customer ID]

        ),

        'Table'[Order Date]

            < EARLIER('Table'[Order Date])  && 'Table'[Rank Date] = EARLIER('Table'[Rank Date]) - 1

    )

)

 

Shift_SalesAmount_Below.JPG

 

Now, we can calculate the difference in Days between 2 orders.

 

Difference in Days = DATEDIFF('Table'[Previous Order Date],'Table'[Order Date],DAY)

 

Difference in Days.JPG

 

PS: This is just one way to solve this kind of problems.

 

 

Comments

its similar to what i have, but what if not every sales order has sales amount - i wish to return the last value available (which is not blank)

Hello,

 

I am trying to create the previous order date formula with similara data table.

All is well but when I get to insert the column name after EARLIER I don't get any prompt for column name.

If I try to insert it manually (date column) I get the error "Parameter is not the correct type".

Is there any solution to that?

 

Thanks in advance.

 

 

Update:

Sorry, found my mistake.

I was trying to create new measure instead of new calculated column. 

I am new to Power BI.

Thanks anyway.

 

 

 

 

Anonymous

Hello,

 

Thanks for this solution. Do you know if there is a way to make it take less memory? The problem I have is that I need to apply it to a case where I have millions of rows, and I can't apply this because there is not enough memory.

 

Thanks and regards

Can we use the same approach for Direct Query?