Rolling 12 WEEK Measure

[MJ_BI]

Hi all,

The funny thing about DAX is that as soon as I feel like I'm getting the hang of things...a new problem pops up which brings me back to earth.  In my current dilemma, I cannot seem to come up with a way to compute a rolling 12 week total for shipped COGS (cost of goods sold).  Here's a bit of background:

In my model there is a fact table named Fact_B2BSalesDiagnostic comprised of several Excel files appended together using PowerQuery.  Each Excel file is a table which reports sales figures for multiple products across a single week in time...where each row represents a specific product's performance in the given week. You can imagine a row in one of the original Excel files as resembling something like:

(ProductNumber, ProductName, UnitsShipped, ShippedCOGS)

While connecting PowerQuery to the folder containing the Excel files, the Week Start and Week End columns were appended to each row.  In this way, we can define a relationship between the Date field of Dim_Calendar and either Week Start or Week End of Fact_B2BSalesDiagnostic in order to define measures which can achieve weekly totals.  The image below depicts the relationship I have used:

Additionally, I have defined YrWkRngKey and YrWkRngSort fields in Dim_Calendar.  The best way to describe these fields is through example:

• Assume we are looking at a row in Dim_Calendar with a Date of 28-Sep-2021
• The week range (sunday to sunday) this date belongs to is 26-Sep-2021 thru 2-Oct-2021
• As such, the YrWkRngKey reads: "2021|9/26-10/2" (Note each row with date between 26Sep21 and 2Oct21 will have this same exact value)
• The YrWkRngSort field is simply an index column which numbers the unique values of YrWkRngKey

Having defined things as such, I was able to create a measure which computes ShippedCOGS for the prior period as follows:

[B2BShippedCOGS - PriorPeriod] :=
CALCULATE (
    [B2BShippedCOGS],
    FILTER (
        ALL ( Dim_Calendar ),
        CONTAINS (
            VALUES ( Dim_Calendar[YrWkRngSort] ),
            Dim_Calendar[YrWkRngSort],
            Dim_Calendar[YrWkRngSort] + 1
        )
    )
)

Where:

[B2BShippedCOGS] := SUM( Fact_B2BSalesDiagnostic[Shipped COGS] )

 However -- I can't seem to find a way to get a rolling 12 week total for shipped COGS!  I've been reading articles and trying things for 2 hours now without any luck, and I'm turning to you folks in the hopes that your expertise can help me get through this!

Thanks in advance for any help you may offer.  Hope you have a good one!

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, I think you can achieve this using a rank column and a calculate column to get the rolling total, you can try my steps:

This is the test data model I created based on your sample pictures:

Create two calculated column in the main table like this:

week rank = RANKX('Fact_B2BSalesDiagnostic',[WeekStart],,ASC,Dense)

rolling 12 week total =

var _value=

CALCULATE(SUM(Fact_B2BSalesDiagnostic[Shipped COGS]),FILTER(ALL(Fact_B2BSalesDiagnostic),[week rank]<=EARLIER(Fact_B2BSalesDiagnostic[week rank])&&[week rank]>=EARLIER(Fact_B2BSalesDiagnostic[week rank])-11))

return

IF([week rank]<12,BLANK(),_value)

Then you can get the rolling total for 12 weeks in the table like this:

Then you can create a table chart and a slicer to get the expected output like this:

And you can get what you want.

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.