Calculate Non-Rolling 13 Week SUM

Hi Anonymous ,

Sure, we will make explatnation of following formula:

Non-rolling 13 WK Confirmation Rate = 
Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )
Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

The first part, we use ALLSELECTED function to get a table which infused by outer slicer, but ignore the row-level content (such as each rows in table visual)

then we add a addtion column to this table, containing a rank index value based on the week of year, such as week 5, 4, 2 will get a rank by 1,2,3

we put the result table into a variable called "t"

Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )

Then we want to get the rank index value of now selected date (such as one row in table visual), 

we use Max to get the current week value, then use it to find the index value in the table t

MAXX function is used to get the value from a variable table. (After filter the table t, it will ony return several rows contain same week number, they all have same index value)

Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )

Now, we have the week index value of current week and a table contain the week index column. we can use the sum function to get value from a filtered table, just use the condition index in greater than current but lessthan than current index plus 12

CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

Now the logic of this formula is clear, We think the problem may be in the variable i, could you please try to use the following formula?

Non-rolling 13 WK Confirmation Rate Version 2 = 
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ), [Week of Year],, DESC )
    )
VAR i =
    RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ( [Week of Year] ) ),, DESC )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

Also do not forget to format this measure as a percentage value.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.