Using Kusto Query compare a time offset to current data set and calculate the difference

Question

I'm trying to figure out how to compare a current data set for the previous 24 hours to a -7d offset and then display the difference in a percentage. I currently have the following :

let currentB =
    requests
    | where Role == "tech"
    | where success == true
    | where name in ("POST /Auto", "POST Data/Play")
    | summarize request_count = count() by bin(timestamp, 5m);

let OffsetB=
    requests
    | where Role == "tech"
    | where success == true
    | where name in ("POST /Auto", "POST Data/Play")
    | where timestamp >= ago(8d) and timestamp < ago(7d)
    | summarize request_count = count() by bin(timestamp, 5m);

I'm not sure if I need to first format the dates to show only HH:MM or if I can union them and then compare ?

The result set of both queries returns as follows

Timestamp	requestcount
14/03/2024, 10:00:00.000	6

Timestamp	Column B
07/03/2024, 10:00:00.000	2

What i'm trying to achieve is

time	currentB	OffsetB	Difference
10:00	6	2	200%

I've tried to Union and join but not sure I'm fully understanding it, I've also attempted to format the data and project after the summarize but this then just gets me 24 hours of info and not the current 24 hours

Answer 1

Redefine the timestamp value from offsetB dataset by adding 7 days. Join both datasets using inner join based on the matching timestamp value. Then find the difference between both the values and find the difference percentage.

Code:

let currentB =
    requests
    | where Role == "tech"
    | where success == true
    | where name in ("POST /Auto", "POST Data/Play")
    | summarize currentB_count = count() by bin(timestamp, 5m);
let OffsetB=
    requests
    | where Role == "tech"
    | where success == true
    | where name in ("POST /Auto", "POST Data/Play")
    | where timestamp >= ago(8d) and timestamp < ago(7d)
    | summarize OffsetB_count = count() by bin(timestamp, 5m);
    | extend timestamp=timestamp+7d;
currentB
| join kind=inner OffsetB on timestamp
| project currentB=currentB_count, OffsetB=OffsetB_count, PercentageDifference = (currentB_count - OffsetB_count)*100 / OffsetB_count 

Output

currentB	OffsetB	Difference
6	2	200%