How to calculate last N periods? Periods in this case = WeekEnd Date - I want last 8 periods (weeks)

[whatisdata96]

Hi all-

I'm looking to calculate last 8 weeks based on WeekEnd Date which I've already created a calculated column:

WeekEnd Date= dimCalendar[Date]+7-1*WEEKDAY(dimCalendar[Date],1)

Should I use a RANX formula to label the weeks?

I want to be able to show SUM for this period (week) and last 8 periods/weeks.

Would RANKX be a step in the right direction?

Then maybe create that aggregate measure saying SUM(COLUMN), FILTER(TODAY- Last 8 periods)?

I'm all over the place here. Would appreciate any help!

[v-binbinyu-msft]

Hi @whatisdata96 ,

I am really pleased that your issue has been fixed. Would you kindly share with us about your solution here? In this way, it will be beneficial to other community members as well.

Best regards,
Community Support Team_ Binbin Yu

[whatisdata96]

Wow this is wonderful. I wish I was able to do this on my own.

From what I understand, you first created a variable that selected the MAX TODAY WeekEnd Date, then another variable which takes that MAX WeekEND date - 49 days which captures all the data/dates needed for the pivot, correct?

Then Returning the aggregation using the 2 variables as parameters. Am I understanding this correctly?

[Jihwan_Kim]

Hi,

Thank you for your message, and you are correct. 🙂

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

I think, becasue it is calculating week-base, perhaps it is better to use a simple calculation like 7 times 7 periods = 49 days.

Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a solution for your dataset.

Sales last 8 weeks: =
VAR _todaysweekend =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = TODAY () ),
        'Calendar'[WeekEnd Date]
    )
VAR _eightweeksperiodstartdate = _todaysweekend - 49
RETURN
    CALCULATE (
        [Sales measure:],
        KEEPFILTERS ( 'Calendar'[Date] >= _eightweeksperiodstartdate
            && 'Calendar'[Date] <= _todaysweekend )
    )