Subtract values from different rows

[Phil_D]

Hi All,

Been playing around with PowerBi now for about 18 months but need to step the knowledge up as its part of my job now 🙂 Very exciting. I was a heavy excel user so there is a bit of re-training to get my hear around DAX. I am stuck on the following and would appreciate any help. 

What I am trying to do here is to subtract a YEAR_SEMESTER2 from another YEAR_SEMESTER2. e.g. I am grying to get the value for say Number and Algebra in 2019.1 and subtract it from Number and algebra 2018.2. 

I assume that in a new column, I would have the heading as "2019 growth" where the heading and all the values in that column that align with 2019.1 would be the calculation (2019.1-2018.2). So in row 9 in my screenshot, the value in the column would be 4-4=0. Note: This would need to be done via STKEY, DIMENSION_1 and YEAR_SEMESTER2. I would only want the value in the new column 2019 growth for anything that is 2019.1-2018.2.

What I have currently done is to pivit the table against the YEAR_SEMESTER2 and have the column headings as 2019.1, 2018.2 etc. It has created a few issues in other visuals so I was hoping there is a way that I can do as I have explained above. 

Any help would be greatly appreciated. 

Thanks a million. 

Phil

[Anonymous]

Hi there.

First of all, I'd encourage you to read about how to correctly model your data in PowerBI. There are many resources on the web as well as books. If you don't do it, you'll be solving problems that should not exist in a model in the first place, wasting time and effort. Secondly, you should know that in tabular models columns should have a small number of unique values and the data type should be adequate as well. Neglecting these simple rules will lead to disasters later on. I can see that YEAR_SEMESTER2 is stored as a decimal number. This is BAD, VERY BAD. You should store it as text. This, after all, is not a metric but an attribute in a time dimension. Thirdly, the column names... Really? You really want to have an attribute that's called DIMENSION_1? I would change the names to something meaningful and user-friendly. After all, when one looks at a model one should have at least a rough understanding of what individual entities/attributes mean.

And lastly, you should number your semesters consecutively so that it's easy to move back and forth in time across semesters. So, you should have a column that maps YEAR_SEMESTER2 to consecutive integers, min(YEAR_SEMESTER2)=0,1,2,...,n-1=max(YEAR_SEMESTER2). Please, do not store YEAR_SEMESTER2 as a decimal.

[Growth] :=
var currentScore = Table[SCORE_3]
-- You have to have a SemesterNumber column
-- as explained above. 
var currentSemester = Table[SemesterNumber]
var currentDimension = Table[DIMENSION_1]
var currentTopic = Table[Topic]
var currentStkey = Table[STKEY]
var prevScore =
	-- If this does not return just one number,
	-- then you have to check the consistency 
	-- of your table or change the logic.
	CALCULATE( 
		VALUES( Table[SCORE_3] ),
		Table[SemesterNumber] = currentSemester - 1,
		Table[DIMENSION_1] = currentDimension,
		Table[Topic] = currentTopic,
		Table[STKEY] = currentStkey
	)
return
	currentScore - prevScore

If you want to know about DAX, which I can assure you is like nothing you've seen in your life so far, the best source is www.sqlbi.com and book by The Italians (you'll meet them soon enough).

Best

Darek