How do I calculate last 8 weeks using a WeekEND date column?

[whatisdata96]

Hey all - kind of loaded question here. It's been a while since I've had to use any of this here.

So I have a dataset fact table with a date column (1 day per row), and another column that shows how many calls we got on that day. This is the end result I want to come to:

Here is what my data model looks like:

Within the date table, I have created a calculated column which would show when the week ended (which is what I want it aggregated by) - here is the DAX I used to create this WeekEnd Date column for the grouping:

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

This DAX gives me the calculated column I can use for the pivot/visual as it groups by Week (whenever that week ends.

My main issue now is that I want to show last 8 weeks. I created a measure to use to SUM up everything and filter it for the last 56 days (8 weeks) using this DAX:

=CALCULATE(SUM([Calls Offered]),

FILTER(

ALL('SL Data Sheet sht'[Date]), 
[Date] > TODAY() - 56))

When I put everything into a PIVOT, this is what it looks like:

The highlighted number is incorrect because it's not including anything outside of that 56 day range. As days go on, it won't include the beggining of the week on 6/18 because its outside of 56 days.

From what I understand, the only correct solution from here is using the RANKX on the WeekEND Date so I know which are my last 8 periods. Then I can filter on those last 8 WeekEND date periods, instead of having to filter for last 56 days.

A long ramble here, but if anyone could help, I would really appreciate it.

[DataInsights · Accepted solution]

@whatisdata96,

Try this solution.

1. Create calculated column in date table:

Relative Week = 
VAR vToday = TODAY()
VAR vWeekEndDate =
    [Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
    vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
    CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
    vResult

2. Create measure:

Rolling 8 Weeks = 
CALCULATE (
    SUM ( FactTable[Amount] ),
    DimDate[Relative Week] >= -8,
    DimDate[Relative Week] < 0
)

[DaviOliveira]

I'm trying to compare the 8 weeks that preview those to see progression or regression of sales, is there a way I can create a column of comparison?

[DataInsights]

@whatisdata96,

Is this a duplicate post?

https://community.powerbi.com/t5/Desktop/How-do-I-calculate-last-8-weeks-using-a-WeekEND-date-column... 

[whatisdata96]

This is excellent - thank you so much for this. How do you begin to think through the logic? I struggle so much with this (framing the solution in my head)

[DataInsights]

Glad to hear that works. The concept of Relative columns in DimDate is the basis of the logic. Once the Relative columns are created in DimDate, measures can be easily created. A robust date table is essential to any data model.

[whatisdata96]

Do you know if convert and integer is able to be used in Power Pivot? It's not working in Power Pivot for whatever reason.

[DataInsights]

Not sure about Power Pivot. You can try removing CONVERT and INTEGER, or use ROUND.