cancel
Showing results 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.

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

1 ACCEPTED SOLUTION
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 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 =
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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

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 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 =
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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors