Power BI: Calculate difference between data today and yesterday

I had a hard time finding how to calculate the difference between the data yesterday and data today. in Power BI. This documentation is going to show you how I did that.

Set up Data

1) So first I set up my data source. 
It had a Date column, a Category column, and a Data column. That's it, just 3 columns. (for this documentation I changed my data source to 'source')

2) Then I clicked on the Data view on the left bar

3) Next, I created a column that showed the previous date

Date Yesterday = CALCULATE(

MAX('source'[Date]), FILTER('source', 'source'[Date] < EARLIER('source'[Date])))

4) Create a column for the data from the previous day

Data Yesterday = CALCULATE(

SUM('source'[Data]),
FILTER(
'source',
'source'[Date] >= EARLIER('source'[Date Yesterday])
&&'source'[Date Yesterday]<EARLIER('source'[Date Yesterday])
&&'source'[Category]=EARLIER('source'[Category])))

5) Then, I created a column named Difference that calculates the difference between the data

Difference = 'source'[Data] - 'source'[Data Yesterday]

6) From there I was asked to round the data to the nearest 1000. To preserve the original data, I created new columns to do that.

Data Rounded = ROUND('source'[Data],-3)

Data Yesterday Rounded = ROUND('source'[Data Yesterday],-3)

Difference Rounded = 'source'[Data Rounded] - 'source'[Data Yesterday Rounded]

Create Measures

7) Create a # Change measure

This shows the difference from the first data point in the visual and the last data point in the visual

(the person requesting wanted the result rounded to the nearest 1000 again)

# Change =

(CALCULATE(

SUM('source'[Data Rounded]),

FILTER('source','source'[Date]=MAX('source'[Date]))

) -

CALCULATE(

SUM('source'[kWh Use Rounded]),

FILTER('source','source'[Date]=MIN('source'[Date]))))/1000

8) Create a % Change measure

Similarly, this shows the difference % from the first data point in the visual and the last data point in the visual

% Change = DIVIDE( CALCULATE( SUM('source'[Data Rounded]), FILTER('source','source'[Date]=MAX('source'[Date]) )), CALCULATE( SUM('source'[Data Rounded]), FILTER('source','source'[Date]=MIN('source'[Date]))) ,0) - 1

9) I also had to create measures like the previous ones that filtered # change in data based on category

# Change Misc = (CALCULATE( SUM('source'[Data Rounded]), FILTER('source','source'[Date]=MAX('source'[Date])&& 'source'[Category]="Misc") ) - CALCULATE( SUM('source'[Data Rounded]), FILTER('source','source'[Date]=MIN('source'[Date])&& 'source'[Category]="Misc")))/1000

10) And I also created measures like the previous ones that filtered % change in data based on category

% Change Misc = DIVIDE( CALCULATE( SUM('source'[Data Rounded]), FILTER('source','source'[Date]=MAX('source'[Date])&& 'source'[Category]="Misc" )), CALCULATE( SUM('source'[Data Rounded]), FILTER('source','source'[Date]=MIN('source'[Date])&& 'source'[Category]="Misc")) ,0) - 1

And that's it! A lot of DAX, but it accomplished what I needed it to. I'm sure there are better ways of doing this, but this is what I came up with for the project.

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.