2 Replies Latest reply: Jul 11, 2018 8:14 AM by Louise Grauss-Spoormaker RSS

    Accumulation up to and including selected period

    Louise Grauss-Spoormaker

      Hi All,

       

      I'm searching for a formula that shows accumulation up to and including the selected period.

       

      For example: I want the totall revenu of 2018 up to and including period 4

      But I want this without selecting all 4 periods in my filterbox.

      I only want to select period 4.

       

      (Period 4 is an example, it could also be period 5 or 6 or....)

       

       

      The formulas I have tried are:

       

      1.

      num(

      sum({<Boekjaar={$(=max(Boekjaar))},Periode={"<=$(vCurrentPeriode)"}>} [Bedrag Saldo])

      ,'#.##0')

       

      -> This one shows the totall revenu until today.

       

      2.

      rangesum(

      above(

      Sum({<Boekjaar={$(=max(Boekjaar))}>} [Bedrag Saldo]),0,rowno()))

       

      -> This one shows the revenu of only period 4

       

      I think I have to do something with my period in the formula but I have no idea what kind of formula I need.

       

      Can anyone help me with this?

       

      Gr Louise

        • Re: Accumulation up to and including selected period
          Rachel Delany

          Have you tried using the function inyeartodate? Further information could be found here.

           

          Alternatively, how do you set the variable vCurrentPeriode?

          Potentially it may work if you make vCurrentPeriode = MAX(Periode).


          Hope this helps.

            • Re: Accumulation up to and including selected period
              Louise Grauss-Spoormaker

              Hi Rachel,

               

              The formula with MAX(Periode) works!

              Thanks you very much!

              I tried it allready in the formula but that did not work so I made a variable and I called it UpToAndIncludingPeriod.

               

              num(

              -sum({<Boekjaar={$(=max(Boekjaar))},Periode={"<=$(vUpToAndIncludingPeriod)"}>} [Bedrag Saldo])

              ,'#.##0')

               

               

              I also tried inyeartodate but that did not work.

              I probably made a mistake in the formula.

              And Maybe I understood it wrong but do you have to adjust the date in that formula every year?

               

              inyeartodate ('2018/01/01', '2018/12/31', 0)


              num(

              -sum({<Boekjaar={$(=max(Boekjaar))},Periode={$(vInYearToDate)}>} [Bedrag Saldo])

              ,'#.##0')

               

               

              Gr Louise