3 Replies Latest reply: Jun 12, 2018 3:37 PM by Lee Mychajluk RSS

    YTD Totals

    Lee Mychajluk

      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?



        • Re: YTD Totals
          Lee Mychajluk

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


          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.

          • Re: YTD Totals
            Anil Samineni

            Do you need % of YTD?


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

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

              • Re: YTD Totals
                Lee Mychajluk

                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!