Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=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])
Try redefining your variables with date() like:
MaxTransDate1 = date(MaxTransDate-1)
....
Edit: Corrected
Hi,
You should try to use it in the same expression, sum({<Date={">=$(From) <=$(To)"}>} Value)
Instead of variable you can add addmoths or today or any other datefunction
Ok, you want it in 7 different colums?
You must ignore the temporal dimensions that affect the calculation. I imagine analysis similar to September 1 :
=SUM({<[Trans Date]={'$(MaxTransDate1)'},Date=>}[Sales Qty])
Hi
Sorry for relying late. Will try the formula and let you know.
kind regards
Nayan
Hi Tresesco
I have tried the formula, but does not work.
kind regards
Nayan
Hi Staffan
Yes, that is correct.
kind regards
Nayan
Hi Staffan
The formula that I have , I have taken it from a formula that our Outsource company has created. In his formula he has "MonthIndex' (which is Fiscal Year x 12 + Fiscal Period) , which shows the sales quantity per month for the last seven months.
So i used the same formula and just substituted MonthIndex for [Trans Date].
kind regards
Nayan
Can you upload som example?