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

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.

## 0 comments:

## Post a Comment