Previous week results calculation on a week key

[MightyMicrobe]

This has been asked to death, but none of the previous solutions seems to work for me, please help. 

My metric is grouped by Week End label in the calendar table (WE2019-07-05, for example). 

In my calendar table I have the label and the label keys for the week end, like so:

I'm trying to use the following formula which does not work. What am I doing wrong?

Visits PW = 
CALCULATE(
    [Total Visits],
    FILTER(
        ALL('Calendar'), 
        'Calendar'[Week Ending Date Key] = 'Calendar'[Week Ending Date Key] - 1
    )
)

[v-xulin-mstf · Accepted solution]

Hi @MightyMicrobe

You need to modify the measure to:

Visits PW =
VAR WeekEndingDateKey = MIN('Calendar'[Week Ending Date Key])
RETURN
CALCULATE(
    [Total Visits],
    FILTER(
        ALL('Calendar'), 
        'Calendar'[Week Ending Date Key] = WeekEndingDateKey - 7
    )
)

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[notem]

@MightyMicrobe , because of for every row in a table there are checking condition:

'Calendar'[Week Ending Date Key] = 'Calendar'[Week Ending Date Key] - 1

For example, 'Calendar'[Week Ending Date Key] = 20190712, then condition will be

20190712 = 20190712 - 1

So it's always FALSE for every row in table.
Solution:

Visits PW =
VAR WeekEndingDateKey = MIN('Calendar'[Week Ending Date Key])
RETURN
CALCULATE(
    [Total Visits],
    FILTER(
        ALL('Calendar'), 
        'Calendar'[Week Ending Date Key] = WeekEndingDateKey - 1
    )
)

[MightyMicrobe]

Thanks for this. I tried the solution, and while there are no errors, the formula returns blanks in the matrix. 

[notem]

@MightyMicrobe, oops!

'Calendar'[Week Ending Date Key] = WeekEndingDateKey - 7