YTD calculations in Cross table

[Shubh M]

Hello Everyone,

I need to display the cross table showing actual sales for every month and YTD value (if we select 2 diff months in date filter).

But i am running into some issues here.

Detials:

In the attached screenshot, you will see that i show sales and YTD salesfor dept and sub dept for multiple months.

I calculate my YTD for the running fiscal year with this epression:Max(Sum([sales]) OVER (Intersect([Dept],[sub Dept],[FY],AllPrevious([ReportDate]))))

Report Date is date object (dd/mm/yyyy format).

 

I get the correct value for YTD for every sub dept, and it resets fine for every new month. The problem occurs when i add subtotals in the end.

IF you look at the screenshot, what is happening is that it is summing up all the cell values for the dept, which is not right.

I know there is setting in cross table (Totals), with which we can change if the calucation should be on cell values or underlying data. I tried both, but none helping.

 

with Cell values, it sums up everything (as i am showing in scrrenshot)

with underlying data, it looks at my YTD formula and gives me the max value from the list, which is again wrong.

 

I am looking for your help here, if I am doing anything wrong here or if it is known bug or if its not possible in cross tables.

Many thanks in advance and please let me know if any queries on my side.

 

Thanks and Regards

Shubham