PowerPivot Slice one date - filter t-3 weeks

[Ratamahatta123]

Hello everyone,

I have an issue with PP which I cannot solve alone - hope you can help me.

I have weekly data which I would like to compare. The goal is to choose one date per pivot slicer (lets's call it w0) and deliver the remainder two weeks automaticall (w-1 and w-2). Dataset includes several entries per date (always the monday, beginning of the week).

The measure for w0 is the following:

=CALCULATE (
SUM ( flatfile[ValueEUR] );
FILTER(flatfile;flatfile[fcDate]=max([fcDate])))

The measure for w-1 is the following.

=CALCULATE (
SUM ( flatfile[ValueEUR] );
FILTER(flatfile;flatfile[fcDate]=max([fcDate])-7);
FILTER (flatfile; flatfile[Type (Actual/Plan)]="A");
ALL(flatfile[fcDate])
)

Unfortunately when I put in fcDate as a slicer and choose w0 there is no w-1 showing up - only if i have everything selected it does work.
I guess somehow I have to break the link between the data and the slicer - ALL() does not work apparently.

Do you have another idea?

Thanks
Ratamahatta

[rwfigtree]

Is this what you want?
See attached.

mW-1:=var x = MAX(Table1[date])-7
return CALCULATE(SUM(Table1[qty]),FILTER(ALL(Table1),Table1[date]=x))

[Ratamahatta123]

Thanks a lot, that helped yes.
The creation of a variable did the job!

Greets