Total and most of numbers are correct, but several numbers are incorrect. What cause it?

[Anonymous]

Hello, 

I created a measure for the last 13 weeks, I found the Total, and most of the numbers are correct, but several numbers are incorrect. What causes it?

Here is my measure

Last 13 weeks Net Receivables 1 =

VAR LAST_week = CALCULATE( MAX(Data[Week]) ,ALL(Data))

VAR selected_last_week = SELECTEDVALUE(Data[Week])

VAR LAST_13_Week = SUMX(

TOPN( 14,

CALCULATETABLE(

SUMMARIZE( Data,

Data[Week_No]),

ALL(Data[week])),

Data[Week_No],DESC),

'Frt Type'[Total Net Receivables 1])

VAR EXCEPTLASTWEEK = LAST_13_Week - [last_week_net_available 1]

VAR RESULT = IF ( EXCEPTLASTWEEK = 0, BLANK(),LAST_13_Week)

RETURN EXCEPTLASTWEEK

[PaulDBrown · Accepted solution]

I'm not sure if this is what you need and it will probably need tweaking to adjust to you model. However...

Taking this simple dataset as an example:

Data Table

Add a new calculated column to the table to get the YearWeek value for each row:

YearWeek = 'Data Table'[year] * 100 + 'Data Table'[week]

Next create a dimension table for the periods using:

Period Table = 
ADDCOLUMNS (
    SUMMARIZE (
        'Data Table',
        'Data Table'[week],
        'Data Table'[year],
        'Data Table'[YearWeek]
    ),
    "Order", RANKX ( VALUES ( 'Data Table'[YearWeek] ), 'Data Table'[YearWeek],, ASC )
)

Create a single direction relationship between the YearWeek field in the Period Table and the corresponding field in the Data Table:

And finally to get the rolling 13 week value (I'm using a simple SUM measure as the value to be calculated), create this measure:

13 Weeks rolling Sum =
VAR _Min =
    MAX ( 'Period Table'[Order] ) - 13
RETURN
    CALCULATE (
        [Sum Sales],
        FILTER (
            ALL ( 'Period Table' ),
            'Period Table'[Order] >= _Min
                && 'Period Table'[YearWeek] <= MAX ( 'Period Table'[YearWeek] )
        )
    )

to get:

If you have actual dates in the data (aswell as weeks), you should be using a date table instead of the Period Table (no need for the YearWeek column in the Data Table in that case)

I've attached the sample PBIX file