Show aggregated/sum of sales for selected date from start date of month

[Fareeda]

Dear All,

I have a requirement where in I need to display  sum of sales for YearToDate, MonthToDate in a Pivot table and  a  sales date filter should be provided where in  user will select  a date, sales data should be aggregated till the selected date.

For example, if I am selecting 6th of January 2019, then  I should get all sum of all sales done from 1st January 2019 till 6th January 2019,in the sales column of Pivot Table. 

Tried many other options but nothing is working out, please help me achieve all three required functionalities in the same Pivot Table.

Thanks in Advance!!!!

Kind Regards,

Fareeda

[Kushal_Chawda]

For YTD

=Sum({<SalesDate ={">=$(=yearstart(max(SalesDate)))<=$(=max(SalesDate))"},Year,Month,Date>}Sales)

For MTD

=Sum({<SalesDate ={">=$(=monthstart(max(SalesDate)))<=$(=max(SalesDate))"},Year,Month,Date>}Sales)

[Fareeda]

Thanks for the solution provided.

I got YTD & MTD but when I am selecting sales Date from filter(supposed to get the aggregated sales value till selected sales date) all other fields are disappearing in the Pivot Table. 

Requirement is in Pivot Table I should get Sales value for YTD, MTD and also aggregated/sum of sales value till the selected sales date from starting of that particular month.

I tried implementing all three (YTD,MTD & Sales Date Filter) in a single pivot table, but not getting it.  If anyone can help me will be great.  

Please feel free to write me if I am not clear in my requiement.

Thanks in Advance!!!

Fareeda

[lorenzoconforti]

Try this:

For YTD

=Sum({1<SalesDate ={">=$(=yearstart(max(SalesDate)))<=$(=max(SalesDate))"},Year,Month,Date>}Sales)

For MTD

=Sum({1<SalesDate ={">=$(=monthstart(max(SalesDate)))<=$(=max(SalesDate))"},Year,Month,Date>}Sales)

If it doesn't work, can you please upload your dashboard so that we can have a look?

[Kushal_Chawda]

make sure that you are field names highlighted are named correctly

For YTD

=Sum({<SalesDate ={">=$(=yearstart(max(SalesDate)))<=$(=max(SalesDate))"},Year,Month,SalesDate>}Sales)

For MTD

=Sum({<SalesDate ={">=$(=monthstart(max(SalesDate)))<=$(=max(SalesDate))"},Year,Month,SalesDate>}Sales)