VirajR's Blog
Compare the current day's data with the previous day's data in Power BI.

Compare the current day's data with the previous day's data in Power BI.

Viraj Rathnayaka's photo
Viraj Rathnayaka
·

3 min read

In this blog post, Let's discuss how to use Power BI DAX to compare the current day's data with the previous day's data in the current context.

We'll do it by using the stock price of 5 top US companies and then move on to,

  • Compare the current day's stock price with the previous day's stock price.

  • Get the difference between the stock price of the current day and the previous day's stock price.

  • Add arrow icons based on the price difference direction.

Compare the current day's vs the previous day's stock price - using PREVIOUSDAY()

We have 5 top US companies and their stock price for 2 consecutive days. (March 30 2023 and March 31 2023).

I'm going to create a table visualization on the report canvas for now. Later, when the calculation is done you can turn it into a different visualization according to your need.

A slicer here is added just to demonstrate values from different days in the data set.

create a new measure and add this formula.

Stock price previouse day =
CALCULATE (
    SUM ( 'Product Prices'[Stock Price] ),
    PREVIOUSDAY ( 'Product Prices'[Date] )
)

Add the new measure to the tale and select a day to check.

The measure pulls prices from the previous day of the selected date period.

In the formula, the DAX function PREVIOUSDAY ( ) returns the last day's date in the current context as a filter in the CALCULATE ( ) function. The example above returns March 30 2023 price. If the current context is March 30, 2023, it returns blank as we do not have data older than that.

Compare the current day's vs the previous day's stock price - using DATEADD ()

We can use DATEADD() function to create the same measure.

Stock price previous day =
CALCULATE (
    SUM ( 'Product Prices'[Stock Price] ),
    DATEADD ( 'Product Prices'[Date], - 1, DAY )
)

DATEADD () function returns a date based on input arguments (Interval number, Interval). In this formula, it returns the date from one day back.

Get the difference between the current day vs the previous day's stock price.

create a new measure and add this formula.

Price Difference = 
SUM ( 'Product Prices'[Stock Price] )
    - CALCULATE (
        SUM ( 'Product Prices'[Stock Price] ),
        PREVIOUSDAY( ( 'Product Prices'[Date] )
    ))

Add the new measure to the table.

Add arrow icons based on the price difference direction.

Let's try to add an indicator icon based on the price difference. Here is the rule set.

To do this go to the column you want conditional formatting.

Add the below rules to the "Price Difference" column.

Then the table will look like this.

Conclusion

You can use a time-series dataset to get old data and compare it with current data using DAX measures, calculate differences, and add an indicator icon based on the calculated value.

PowerBIdax