PowerPivot DAX Date Lookup

[gavs73]

Would appreciate any help with this, I have a PowerPivot table, and I want to add a Dax column to find the sale by customer the previous week, for each customer on date dd/mm/yy find the value for (dd/mm/yy-7days). Thanks in advance for your help. Gav

 

[mart37]

Calculated Column: = LOOKUPVALUE(Table1[Value],Table1[Date],(Table1[Date])-7)

 

[gavs73]

Is there a way to reference the Company and Date in the same table, in Excel using SUMIFs it would be like this, not sure if it is possible in DAX... Thanks

 

[gavs73]

=SUMIFS(C1:C10,A1:A10,A10,B1:B10,B10-7)

 

[mart37]

Not as a calculated column but as a measure:

PreviouWeekCustomer:=
VAR a = MAX ( 'Table1'[Date] )-7
VAR d =
CALCULATE (
MAX ( 'Table1'[Date] );
FILTER ( ALL ( 'Table1' ); 'Table1'[Date] < a );
VALUES ( 'Table1'[Customers] )
)
RETURN
CALCULATE (
SUM ( 'Table1'[Value] );
FILTER ( ALL ( 'Table1' ); 'Table1'[Date] = d );
VALUES ( 'Table1'[Customers] )
)

 

[gavs73]

Not as a calculated column but as a measure:

PreviouWeekCustomer:=
VAR a = MAX ( 'Table1'[Date] )-7
VAR d =
CALCULATE (
MAX ( 'Table1'[Date] );
FILTER ( ALL ( 'Table1' ); 'Table1'[Date] < a );
VALUES ( 'Table1'[Customers] )
)
RETURN
CALCULATE (
SUM ( 'Table1'[Value] );
FILTER ( ALL ( 'Table1' ); 'Table1'[Date] = d );
VALUES ( 'Table1'[Customers] )
)

thank you so much for your help