Getting Previous Values in Power BI - Part 1

[harshnathani]

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.

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.

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

    )

)

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.

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

  

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

    )

)

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

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

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

Previous Values_Calculated Columns.pbix