date function

[ilanbaruch]

how can i compare between this year to last year sales in a pivot?

[saumyashah90]

D1=Date(DateField,'DD-MM-YYYY')

vCurYearDate1=Date(DateField,'DD-MM-YYYY');

vPrevYearDate=Date(AddYears(DateField,-1),'DD-MM-YYYY');

You can us this two variables

in your expression such as

sum({$<Field=$(vCurYearDate1)>} Sales);

sum({$<Field=$(vPrevYearDate)>} Sales);

[vikasmahajan]

Hii,

Following expressions

For Curr Year

=sum($<FiscalYear={$(=max(FiscalYear))}>} Sales)

for Last Year

=sum($<FiscalYear={$(=max(FiscalYear)-1)}>} Sales)

Vikas

[jvitantonio]

You can either add the field Year as a dimension or, if you don't want to add Year as a dimension, you can do:

Dimension tab: client (example)

Expression:

1) First expressions, called "Sales TY": sum({$<Year = $(=year(today())) >} Sales)

2) Second Expressions, called "Sales LY": sum({$<Year = $(=year(today())-1) >} Sales)

This will give you the Sales for this year and last year for the dimension Client.

Make sure you have in your model a field Year.

Hope this helps.

JV

[ilanbaruch]

it helped,

thank you

[#6]

Select year as a dimension

=sum({<Date={$(vmaxyear)},FinancialYear=,FinMonthName=>}Scores)

vmaxyear=max(year(Date))

=sum({<Date={$(vmaxyear-1)},FinancialYear=,FinMonthName=>}Scores)