How to subtract values between months for each group and each year separately in R
Does this work?
library(tidyverse)
mydata %>%
  # Filter on months 6 and 7
  filter(str_sub(month_id, -2) %in% c("07", "06")) %>%
  # Sort by Key + yearmonth
  arrange(MDM_Key, month_id) %>%
  # Group by Key
  group_by(MDM_Key) %>%
  # Calculate difference between sales
  mutate(sale_diff = sale_count - lag(sale_count)) %>%
  # Return unique values for each Key + year
  filter(str_sub(month_id, -2) == "07") %>%
  mutate(year = as.integer(str_sub(month_id, 1, 4))) %>%
  select(year, MDM_Key, sale_count = sale_diff)
# A tibble: 12 x 3
# Groups:   MDM_Key [2]
    year MDM_Key sale_count
   <int>   <int>      <int>
 1  2012       1    -829881
 2  2013       1     -97422
 3  2014       1   -4785612
 4  2015       1   -2192138
 5  2016       1     756600
 6  2017       1    1968317
 7  2012       2   -1659762
 8  2013       2    -194844
 9  2014       2   -9571224
10  2015       2   -4384276
11  2016       2    1513200
12  2017       2    3936634


i have such data example
mydata=structure(list(month_id = c(201206L, 201206L, 201207L, 201207L, 
201306L, 201306L, 201307L, 201307L, 201406L, 201406L, 201407L, 
201407L, 201506L, 201506L, 201507L, 201507L, 201606L, 201606L, 
201607L, 201607L, 201706L, 201706L, 201707L, 201707L), MDM_Key = c(1L, 
2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 
2L, 1L, 2L, 1L, 2L, 1L, 2L), sale_count = c(6978517L, 13957034L, 
6148636L, 12297272L, 6147466L, 12294932L, 6050044L, 12100088L, 
8127548L, 16255096L, 3341936L, 6683872L, 8995701L, 17991402L, 
6803563L, 13607126L, 7098546L, 14197092L, 7855146L, 15710292L, 
5575453L, 11150906L, 7543770L, 15087540L)), class = "data.frame", row.names = c(NA, 
-24L))

I need for each group (mdm_key) to get the difference between the months (07-06) by sale_count Variable. The data contains histories by year. e.g. 06(june) and 07(jule) monthes for 2012,
06 and 07 for 2013 and so on...
For each year and each mdm_key I need to subtract the sale_count from month 7 to month 6.
So that the desired result for each year and each group mdm_key looks something like this
   year MDM_Key sale_count
1  2012       1    -829881
2  2013       1     -97422
3  2014       1   -4785612
4  2015       1   -2192138
5  2016       1     756600
6  2017       1    1968317
7  2012       2   -1659762
8  2013       2    -194844
9  2014       2   -9571224
10 2015       2   -4384276
11 2016       2    1513200
12 2017       2    3936634

What is the easiest way to do such a subtraction?
Thanks for your any help.
