YTD Totals

[Anonymous]

I have the YTD Total based on the selections usign this formula:

Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

I'm putting it in a Pivot Table and using FY as a dimension, so it seems to work OK.

What I need is the Total Revenues for the Year, YTD, so I can get a % of YTD Revenues based on the current selections.

I thought the formula would just be:

Sum({1<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

But, that didn't seem to work. (I think that gives me the total for ALL years.)

What would I need to do to have the formula recognize the FY dimesion from the Pivot Table?

Thanks!

[Anonymous]

Sum(Total {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

and

Sum(All {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

I should add that both of the above return the same results. I would think that ALL would give me what I'm seeing on my formula above, but TOTAL would give me the result I was looking for, given that the field Asset_FiscalYear is a dimension in the Pivot Table.

[Anil_Babu_Samineni]

Do you need % of YTD?

Num(Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])/

Sum(TOTAL {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue]),'##,#0%')

[Anonymous]

Sorry for the late reply - I was away on vacation and am just getting back to this...

This portion of your formula seems to give me the correct value (YTD [Total Revenue]):

Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

The problem with this part of the formula:

Sum(TOTAL {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])

When used in a Pivot, it doesn't respect the dimension, so when I put the FY as a Column, the formula doesn't give me the fiscal year Total, it gives me the Total for ALL fiscal years. I need the formula to respect the FY column in the table as well as having it respect any additional selections.

I hope that makes sense...Thanks for the reply!