Previous period calculation in Azure Data Explorer\Kusto

Question

We are building a report in Power BI on data sitting in Azure Data Explore r.Because we need the report to be fully dynamic we cannot pre-write the queries but must rely on Power BI to generate the queries for Data Explorer according to the user actions on the report.

One of our requirements is to show several measures compared to their value on previous period (month). The measure must also be very dynamic and so the correct value must be based on the user filters and actions and can't be pre-calculated.

We added a calculated measure in Power BI:

Prev_Month_Amt=CALCULATE(SUM(sales[Amt]),DATEADD(dates[Record_DT],-1,MONTH))

The dates table contains one row per day and is linked to the sales table in Power BI using Many-to-one relationship. The sales table includes several hundred millions records.

The problem is that when we add the Prev_Month_Amt measure to a Power BI object like Matrix we encountered very long run times and quite often "ge Accumulated string array getting too large" errors.

Is there a better way to build previous period calcualtions in Power BI based on Azure Data Explorer?

Thanks, H.G.

Avnera Avnera · Accepted Answer · 2019-05-30T13:06:15

You can add the previous month amount column to the Kusto table presented to PBI (either by adding it to a real table using update policy or Microsoft flow, or by extending it in a stored function). The PBI will see it as a regular column, here is an example:

let T = datatable(Amount:double, Day:datetime, LineItem:string, Account:string) 
[2, datetime(2019-01-03), "revenue", "a",
2, datetime(2019-01-05), "revenue", "a",
5, datetime(2019-01-03), "revenue", "b",
5, datetime(2019-01-05), "revenue", "b",
10, datetime(2019-02-07), "revenue", "a",
2, datetime(2019-02-10), "revenue", "a",
3, datetime(2019-02-10), "revenue", "b",
4, datetime(2019-02-10), "revenue", "b"
];
T
| extend Month = startofmonth(Day)
| summarize Amount = sum(Amount) by Month, LineItem, Account
| join kind=leftouter 
 (
    T 
    | extend Month = startofmonth(endofmonth(Day) + 1d) // sets the current month to the next month
    | summarize LastMonthAmount= sum(Amount) by Month, LineItem,  Account
) on Month, LineItem, Account
| project Month, LineItem,  Account, Amount, LastMonthAmount

Previous period calculation in Azure Data Explorer\Kusto

1 Answers