Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Do you need % of YTD?
Num(Sum({<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue])/
Sum(TOTAL {<Asset_FiscalMonth={"<=$(=vMaxFiscalMonth)"}>} [Total Revenue]),'##,#0%')
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!