Showing Info for the last 7 Days sales

[#1]

Hi

I am creating a chart table where I want to show Sales Quantity per day, for the last 7 days sales.  The 2 dimensions are [Trans Date] & [Sales Qty] . 

The last date (or max date) sales quantity formula works (see formula below), however when I apply similar formula to the other dates, it does not work. I have used variable (from variable overview) for my formulas. (see screen shot attached)

Please can you tell me where I am going wrong in my formula

Does work

=SUM({<[Trans Date]={'$(MaxTransDate)'}>}[Sales Qty])  - This works

Don't work

=SUM({<[Trans Date]={'$(MaxTransDate1)'}>}[Sales Qty]) 

=SUM({<[Trans Date]={'$(MaxTransDate2)'}>}[Sales Qty]) 

=SUM({<[Trans Date]={'$(MaxTransDate3)'}>}[Sales Qty]) 

=SUM({<[Trans Date]={'$(MaxTransDate4)'}>}[Sales Qty]) 

=SUM({<[Trans Date]={'$(MaxTransDate5)'}>}[Sales Qty]) 

=SUM({<[Trans Date]={'$(MaxTransDate6)'}>}[Sales Qty]) 

=SUM({<[Trans Date]={'$(MaxTransDate7)'}>}[Sales Qty]) 

kind regards

Nayan

[#2]

Hi Roberto

Thank you for your feedback.  However the formula does not work.  There is a red underline on the second date.  See screenshot attached.

kind regards

Nayan

[PradeepReddy]

try something like this...

=SUM({<[Trans Date]={'$(date(MaxTransDate1))'}>}[Sales Qty])

[#4]

Me podías pasar el fichero .qvw

Lo de la expresión Date era un ejemplo. No conozco el modelo que tienes montado. Pero quédate con la idea de obviar los filtros y/o selecciones temporales.

Saludos.

[stabben23]

Date is the selection date you use, Date here is just a dummyfield I guess.

[#6]

Hi Staffan

I would love you load my QV Model but its too big and info is sensitive.  I'll make a sample using excel as my database.

kind regards

Nayan

[#7]

yes, that is correct

[Kushal_Chawda · Accepted solution]

=SUM({<[Trans Date]={"$(=date(max([Trans Date])-1))"}>}[Sales Qty])

=SUM({<[Trans Date]={"$(=date(max([Trans Date])-2))"}>}[Sales Qty])

=SUM({<[Trans Date]={"$(=date(max([Trans Date])-3))"}>}[Sales Qty])

.

.

=SUM({<[Trans Date]={"$(=date(max([Trans Date])-7))"}>}[Sales Qty])

[#9]

Hi Kush

Your formula worked.  Thanks .  Much appreciated.

kind regards

Nayan