Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

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

jennifermemeda_1-1657812141640.png

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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 TableData 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 )
)

 

 

Period.png

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

model.png

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:

13w.pngIf 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

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

1 REPLY 1
PaulDBrown
Community Champion
Community Champion

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 TableData 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 )
)

 

 

Period.png

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

model.png

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:

13w.pngIf 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

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.