Calculate YTD up to specific Date

[khaled]

Hello All,

I have a requirement to calculate the sum of sales from the start of the current year up to a selected date. let's say Today's date

which is 13-Dec-2020 

so data will be from 01-Jan-2020 to 13-Dec-2020 

Plus to calculate the same from the previous year up the same peer date 

from 01-Jan-2019 to 13-Dec-2019

I use the below but it doesn't work at all 

sum({<[Trx Date]=,Year=,Month=,Quarter=, [Trx Date]={">=$(=YearStart(Max([Trx Date])))<=$(=Today())"}>}Amount)

how to do this. please

[Anil_Babu_Samineni]

Kindly share sample data set to handle this.

[khaled]

Hello Anil,

Do you want me to share excel file data ?

or the qvd itself

[khaled]

Hi Anil,

Kindly Check the Sample Data

[lfholland]

In our load statement for our calendar table we create the following flag:

//Our "Link" table contains the transactions dates from our fact table.

Temp:
Load
min(TransactionDate) as minDate,
max(TransactionDate) as maxDate
Resident Link;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

//Then in our Master Table script, we have the following statement

if(year(TempDate)<Year(addyears(Today(),-1)),1,(inyeartodate(makedate(year(today()), month(TempDate), day(TempDate)), today(), 0) * -1)) as CurPrevYTD,

We then use the flag in a set analysis formula like such:

sum({<Year={$(=Max(Year)-1)}, CurPrevYTD={1}>} Sales)