Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculating time-over-time change over irregular intervals

Hello,   I would like to calculate the delta of a value between dates that occur at non-regular intervals, grouped by category. A simplified example of the data is below. I know there ...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous , I am open to other approaches and would appreciate if you would explain your proposal. Thanks. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

OK. This should be done in PQ for sure. If the table is reasonably small, you could do this:

 

[Previous Date] = -- calculated column
var __currentDate = TSD[Date]
var __currentCat = TSD[Category]
var __prevDate =
	MAXX(
		FILTER(
			TSD,
			TSD[Date] < __currentDate
			&&
			TSD[Category] = __currentCat
		),
		TSD[Date]
	)
return
	__prevDate
	
[Previous Value] = -- calculated column
var __currentCat = TSD[Category]
var __prevDate = TSD[Previous Date] -- calculated column above
var __prevValue =
	MAXX(
		FILTER(
			TSD,
			TSD[Date] = __prevDate
			&&
			TSD[Category] = __currentCat
		),
		TSD[Value]
	)
return
	__prevValue

Now you're able to create another calculated column that will hold the difference in values. But you could also create only one calculated column telescoping the formulas into one...

 

Best

Darek