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
Note: Only a member of this blog may post a comment.