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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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