cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How to calculate last year Rolling 4 weeks

Rolling 4 weeks Sales =
VAR Weeks =
DATESBETWEEN (
'Date'[Date],
MAX ( 'Date'[Date] ) -21,
MAX ( 'Date'[Date] )
)
VAR Result =
CALCULATE ( [Sales], Weeks)
RETURN
Result
How do i calculate last year rolling 4 weeks?(same time last year based on date selection)
@Greg_Deckler @Uzi2019
3 ACCEPTED SOLUTIONS
Super User
DATESBETWEEN (
'Date'[Date],
MAX ( 'Date'[Date] ) -21-364,
MAX ( 'Date'[Date] -364)
)
Resolver II

``````Rolling 4 Weeks Sales Previous Year =
VAR SelectedDate = MAX ( 'Date'[Date] )
VAR SameDateLastYear = EDATE(SelectedDate, -12)
VAR WeeksLastYear =
DATESBETWEEN (
'Date'[Date],
SameDateLastYear - 21,
SameDateLastYear
)
VAR Result =
CALCULATE ( [Sales], WeeksLastYear)
RETURN
Result``````

I would try this. Then, whenever you set a date (e.g., via a date slicer or on a visual), it does the following:

Sets that as the new max of the date table for the selected context

The second variable determines the same date in the previous year based on the selected value

Then we have dates between SameDateLastYear and the 21 preceding. Then we're calculating Sales based on that.

Both mine and lbendlin's solution above are similar, but lbendlin's solution always looks at a fixed period (385 - 364 days ago) while solution above looks at 4 week period ending on the same date in the previous year. Could lead to different results if factoring in leap years.

Super User

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

DAX TI measures are the devil. Don't use them. Friendly advice.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
9 REPLIES 9
Community Support

Thank you @Greg_Deckler , @lbendlin  and @Jamie_Scott  for your prompt replies.

Hi @Beerlover  ,
May I ask if you have tried the suggestions above? If the suggestion helped you, you might consider marking it as a solution. If the problem has not yet been solved, please feel free to ask us a question.

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thank you everyone for the response. I am checking it now and will respond soon.

Super User

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

DAX TI measures are the devil. Don't use them. Friendly advice.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Resolver II

``````Rolling 4 Weeks Sales Previous Year =
VAR SelectedDate = MAX ( 'Date'[Date] )
VAR SameDateLastYear = EDATE(SelectedDate, -12)
VAR WeeksLastYear =
DATESBETWEEN (
'Date'[Date],
SameDateLastYear - 21,
SameDateLastYear
)
VAR Result =
CALCULATE ( [Sales], WeeksLastYear)
RETURN
Result``````

I would try this. Then, whenever you set a date (e.g., via a date slicer or on a visual), it does the following:

Sets that as the new max of the date table for the selected context

The second variable determines the same date in the previous year based on the selected value

Then we have dates between SameDateLastYear and the 21 preceding. Then we're calculating Sales based on that.

Both mine and lbendlin's solution above are similar, but lbendlin's solution always looks at a fixed period (385 - 364 days ago) while solution above looks at 4 week period ending on the same date in the previous year. Could lead to different results if factoring in leap years.

Frequent Visitor
R4W_1 =
VAR SelectedDate = MAX ( 'Date'[Week] )
VAR SameDateLastYear = EDATE(SelectedDate, -12)
VAR WeeksLastYear =
DATESBETWEEN (
'Date'[Week],
SameDateLastYear - 21,
SameDateLastYear
)
VAR Result =
CALCULATE ( [Other Sales Volume], WeeksLastYear)
RETURN
Result

Week column has Date saved in mm/dd/yyyy

The result is coming as Blank
Super User

If you want to compare like for like you need to go with 364 (which will work regardless of leap years).  SAMEPERIODLASTYEAR  will have a weekday shift.

Super User
DATESBETWEEN (
'Date'[Date],
MAX ( 'Date'[Date] ) -21-364,
MAX ( 'Date'[Date] -364)
)
Frequent Visitor

Week column has Date saved in mm/dd/yyyy

Super User

You have a misplaced closing parenthesis in row 4.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.