calculate delta between rows based on specific groups in a column

[saipawar]

Hi, 

Here's a snapshot of my data - 

I'd like to create a new column/measure which calculates the difference of the 'Value' based on previous 'Month''s 'Value. The delta should also check the 'Category 4 = Reviews in Month' and for every 'Category 1'. 

Note - 'Month' is not sorted in the dataset by ascending order.

For example - 

here's the desired output example. 

Category 3	Category 4	Month	Value	Delta
Meeting	Reviews in Month	January 2020	103	-
Meeting	Reviews in Month	February 2020	117	14
Meeting	Reviews in Month	March 2020	128	11
Meeting	Reviews in Month	April 2020	173	45
Teams	Reviews in Month	January 2020	200	-
Teams	Reviews in Month	February 2020	200	0
Teams	Reviews in Month	March 2020	201	1
Teams	Reviews in Month	April 2020	202	1

[V-pazhen-msft · Accepted solution]

@saipawar 

Please try something like the following :

Delta  = 
  VAR __PreviousMonth = CALCULATE(SUM([Month].[MonthNo]),FILTER('Table',[Month].[MonthNo]=EARLIER('Table'[Month].[MonthNo])-1))
  VAR __Previous = CALCULATE(SUM([Value]),FILTER('Table',[Month].[MonthNo]=__PreviousMonth), FILTER('Table','Table'[Category 3]=EARLIER('Table'[Category 3]) && 'Table'[Category 4]=EARLIER('Table'[Category 4])))

RETURN
IF([Month]=MIN([Month]),BLANK(),[Value]- __Previous)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@saipawar  - Should be something along the lines of:

Delta =
  VAR __PreviousDate = MAXX(FILTER('Table',[Category 3] = EARLIER([Category 3]) && [Category 4] = EARLIER([Category 4])),[Month)
  VAR __Previous = MAXX(FILTER('Table',[Category 3] = EARLIER([Category 3]) && [Category 4] = EARLIER([Category 4]) && [Month] = __PreviousMonth),[Value])
RETURN
  [Value] - __Previous