cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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!

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

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors