I wanna show the last 12 months, and each of those months should show the sum of 12 months back. So January 2022 shows sum of January 2021 -> January 2022, February 2022 shows sum of February 2021 -> February 2022 and so on.
I new in kusto, seems i need use pivot mode with prev function but these month period a bit confusing.
If you know for sure that you have data for each month, this will do the trick.
If not, the solution will get a bit more complicated.
The Idea is to create an accumulated sum column and then match each month accumulated sum with this of the same month from the previous year.
The difference between them is the sum of the last 12 months.
// Data sample generation. Not part of the solution.
let t = materialize(range i from 1 to 10000 step 1 | extend dt = ago(365d*5*rand()) | summarize val = count() by year = getyear(dt), month = getmonth(dt));
// Solution starts here.
t
| order by year asc, month asc
| extend cumsum_val = row_cumsum(val) - val, prev_year = year - 1
| as t2
| join kind=inner t2 on $left.prev_year == $right.year and $left.month == $right.month
| project year, month = format_datetime(make_datetime(year,month,1),'MM') , last_12_cumsum_val = cumsum_val - cumsum_val1
| evaluate pivot(month, any(last_12_cumsum_val), year)
| order by year asc
| year | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2018 | 1901 | 2020 | 2018 | 2023 | 2032 | 2039 | 2015 | 2025 | 2039 | |||
| 2019 | 2045 | 2048 | 2029 | 2043 | 2053 | 2040 | 2041 | 2027 | 2025 | 2037 | 2050 | 2042 |
| 2020 | 2035 | 2016 | 2024 | 2022 | 1999 | 2009 | 1989 | 1996 | 1975 | 1968 | 1939 | 1926 |
| 2021 | 1926 | 1931 | 1936 | 1933 | 1945 | 1942 | 1972 | 1969 | 1981 | 2007 | 2020 | 2049 |
| 2022 | 2051 | 2032 | 2019 | 2002 |