Rolling customer order tally

[jessewysong]

Hello,

Amateur at work here... I would like to build a dashboard for each sales rep that shows if an assigned customer has ordered in each particular week and limit it to a rolling total of the 4 previous weeks. We are in a business where not all customers order weekly (but if we haven't heard from them in a month its a problem). I am envisioning rows of customer names down the left, columns for this week, previous week, 3 weeks ago, 4 weeks ago and indicating a true/false in the "cell" if an order was placed. And if they ordered their sum for that week in the invoice amount field would be > 0 I guess? 

All our data is in a SQL database so all data should be fair game. I am only used to using Crystal Reports which is unweildy and much more static than Power BI.

At the top level I am trying to figure out how to filter a dashboard or report to a rolling time period when opening or refreshing the data, without the user having to select a date range. Any help would be appreciated. 

Thank you!

[greggyb]

This is pretty simple. You have several options.

The problem - display sales for each customer for a rolling 4 weeks.

I'll assume you have a customer dimension, a date dimension, and a sales fact.

Option 1: Add a field to filter on Rolling 4 Weeks

You'll need to add a column to your date dimension. If you can modify the view in SQL Server, do it there. I'll assume a rolling 28 days, but these can be modified to use calendar week boundaries if you want.

If you can't modify SQL Server views, then add the field in Power Query.

If you want to not use Power Query, change your mind. You can do the same in DAX.

--TSQL

CREATE VIEW DimDate AS
SELECT
    ....
    ,Rolling4Weeks =
        CASE
        WHEN [Date] > DATEADD( DAY, -28, GETDATE() )
            AND [Date] <= GETDATE()
        THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
        END
FROM DateTable



//Power Query

let
  Today = DateTime.Date( DateTime.LocalNow() )
  ,Rolling4 =
    [Date] > Date.AddDays( Today, -28 )
      and [Date] <= Today
in
Rolling4


//DAX

Rolling4Weeks =
DimDate[Date] > TODAY() - 28
    && DimDate[Date] <= TODAY()

Then you can just apply a visual, page, or report level filter on Rolling4Weeks = True. The view, Power Query, or DAX will update every night as your model refreshes. Rolling4Weeks will always be true for the rolling 4 week period.

Alternately, you could make a measure in the Power BI data model:

TotalSales =
SUM( FactSale[SalesAmount] )

Rolling4WeeksTotal =
IF(
    MAX( DimDate[Date] ) > TODAY() - 28
    ,[TotalSales]
)

Your visualization is as simple as a matrix with customers in the rows area, and weeks in the columns area. You either need to use the filter field *OR* the [Rolling4WeeksTotal] measure. No harm in combining them, but no necessity, either.