YTD calculation for each date

[deblina_rai]

Date	Acc	Rep	Other Dims	Trxn Amt	YTD
1/5/2019	AccA	RepX	XXXX	25	25
2/10/2019	AccA	RepX	XXXX	75	100
7/15/2019	AccA	RepX	XXXX	80	180
2/10/2019	AccB	RepY	YYYY	20	20
7/18/2019	AccB	RepY	YYYY	40	60

I need YTD column to be populated for each row.

such as for 5th jan it will give sales  from 1st jan to 5th  jan

for 2/10/2019 it will give sales from 1st jan to 10 the feb  and so on 

Please help me on that

[StarinieriG]

Hi,

try this:

RangeSum(Above(Sum([Trxn Amt]),0,RowNo()))

[deblina_rai]

Rangesum will not work that i tried. 

I need YTD amout for each date

[sunny_talwar]

Why will RangeSum not work? Would you be able to elaborate on the issue you see when you use RangeSum?

[deblina_rai]

i am using this. Can you please help me on the  end date part (underlined)syntax?

num(sum({<[Process Date]={">=$(=YearStart(Max([Process Date]))) <=$(=[Process Date]=aggr(nodistinct max([Process Date]),[Firm Name],[Advisory Platform Ind],[TA Alias Rep ID],[Rep First Name],[Rep Last Name],[Rep City],[Rep State Code],[Rep Zip Code],[Acct Num],[TA Prod Code],[Voluntary Tran Desc],[Process Date]))"}

,[Process Month]=>}[Sales Amt]),'#,##0')

[sunny_talwar]

What does this expression has to do with RangeSum? I thought you tried an expression with RangeSum which didnot work for you? or are you considering the below expression to be the RangeSum expression?

[deblina_rai]

for  rangesum   I tried this below:

aggr(rangesum(above( Sum({$<[X529 Special Trade Ind]={'Y'},
[Advisory Platform Ind]-={"=len([Advisory Platform Ind])=0"}>}[Sales Amt]) ,0,rowNo())),[Process Date])

But the range limit we cant set 0 and  end limit also we cant take rowno() as  it will just sum  up with above column irrespective  of  process date and also after using aggr on  rangesum  it was not working.

I need YTD amount for each date in each row.

1st jan to 15th july

1st jan to 16th july   so on like this. SO range sum will not work.

[deblina_rai]

Now  I am using this set analysis. But syntax is not working for end date.

[sunny_talwar]

It will be difficult to give you an exact expression, but you do need to use RangeSum here... Set analysis cannot calculate YTD if you have date as one of the dimension... in order to do that, you need to use RangeSum or AsOfTable 

[deblina_rai]

Rangesum need to set  the range as year start date and end date as that date .

like 5th jan if the date then it will be 1st jan as upper limit and 5th jan as lower limit.  so in this case hoe rangesum will work?