Running Totals- Best Strategies to Provide Flexibility

[pswigert]

I know there are a lot of answers on related topics already, but they all seem focused more on overall cumulative totals or on specific scenarios.

At a conceptual level, I've got some simple fields: EventDateTime and Value being pulled from a SQL Server query.

If I want to set up a report that can easily do rolling window calculations on these and ideally be able to flip between different windows and calculations (e.g. rolling 30 days sum of Value, or rolling 6 month count of Value).

What's the cleanest way to set that up from the beginning? Should I bring in a generic Calendar table and join to that in my query so that the EventDateTime is associated with a simpler date field? Should I add some new calculated columns in DAX?

In something like python's pandas library, the rolling sum function makes this super simple.

http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.rolling_sum.html

In PowerBI, there appear to be a number of messy ways to do it. Any advice on what to start with and what the DAX syntax would be for the proposed solution?

[v-ljerr-msft · Accepted solution]

Hi @pswigert,

---

If I want to set up a report that can easily do rolling window calculations on these and ideally be able to flip between different windows and calculations (e.g. rolling 30 days sum of Value, or rolling 6 month count of Value).

---

I don't aware any clean way to set this up in Power BI Desktop. Firstly, you may need to create different measures for "rolling 30 days sum of Value", "rolling 6 month count of Value", and other calculations you want to do. Then you should be able to follow steps below to create a new measure which can flip between different calculations according to your selection on a Slicer.

1. Import a new table with a column called "Calculation" like below.

2. Create a new measure using SWITCH Function (DAX) like below.

Measure =
SWITCH (
    FIRSTNONBLANK ( 'Table1'[Calculation], 1 ),
    "rolling 30 days sum of Value", measure1,
    "rolling 6 month count of Value", measure2
)

3. Show the measure above on the report with a Slicer of "Calculation" column.

Regards