cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

sample_data.PNG

datamodel.PNG

I know there are quick measures for things like year-over-year, or year-over-month change, but I was struggling to adapt these. I did manage to create a calculated column with the previous date in each row grouping by category, and if I can extend this to determine the Value for that previous date, I can take the difference that way... I am open to other approaches as well.


Thank you for your time - Alex

Previous Date = 
    CALCULATE(MAX(TimeSeriesData[Date]), FILTER(TimeSeriesData, EARLIER(TimeSeriesData[Date])>TimeSeriesData[Date] && EARLIER([Category])=TimeSeriesData[Category]))
 
3 REPLIES 3
Anonymous
Not applicable

Why don't you do that in Power Query? This is where you should do it, to be honest.

Best
Darek
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors