How to calculate last year Rolling 4 weeks

[Beerlover]

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 

[lbendlin · Accepted solution]

DATESBETWEEN (

        'Date'[Date],

        MAX ( 'Date'[Date] ) -21-364,

        MAX ( 'Date'[Date] -364)

    )

[Anonymous · Accepted solution]

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. 

[Anonymous]

Thank you @Greg_Deckler , @lbendlin  and @Anonymous  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.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

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

Best Regards,

Ada Wang

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

[Beerlover]

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

[Greg_Deckler · Accepted solution]

@Beerlover Better Rolling Average - Microsoft Fabric Community

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.

[Anonymous · Accepted solution]

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. 

[Beerlover]

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

[lbendlin]

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.

[lbendlin · Accepted solution]

DATESBETWEEN (

        'Date'[Date],

        MAX ( 'Date'[Date] ) -21-364,

        MAX ( 'Date'[Date] -364)

    )

[Beerlover]

Week column has Date saved in mm/dd/yyyy

[lbendlin]

You have a misplaced closing parenthesis in row 4.