cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pswigert Frequent Visitor
Frequent Visitor

Running Totals- Best Strategies to Provide Flexibility

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Running Totals- Best Strategies to Provide Flexibility

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.

 

t1.PNG

 

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.

 

r1.PNG

 

Regards

1 REPLY 1
v-ljerr-msft Super Contributor
Super Contributor

Re: Running Totals- Best Strategies to Provide Flexibility

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.

 

t1.PNG

 

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.

 

r1.PNG

 

Regards