Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
69 | |
62 | |
55 |