2 Replies Latest reply on Dec 17, 2015 7:28 AM by Imran Tajuddin

    Calculated measure last known value of a period

    Piotr Reszka

      Hello,

      I would like to analyze balances of customer accounts on the last registered day of period (Year, Quarter, Month) After searching over the forum I found solution using simple table calculation : IF LAST()=0 THEN attr([Measure]) END.

      It works only if I disaggregate data. The problem is that I have to analize> 50mln rows, after turning off Aggregate Measures option performance is so poor that I can do anything.

       

      Does anybody know other solution?

       

       

      Piotr Reszka

        • 1. Re: Calculated measure last known value of a period
          Dimitri.B

          So you want to roll days into months (quarters, etc.) and at the same time retain values for the last day of those months, etc.? If yes, I can only think of doing it in data (custom SQL) or using pass-through functions.

          • 2. Re: Calculated measure last known value of a period
            Imran Tajuddin

            HI,

            This is my first time posting anything, let alone a solution so bear with me. You could try this:

             

            1. Create a calculated field 'Max date per mark' as {INCLUDE:max([Date])}

            2. Create another calculated field reference the calculated field from step 1 which should look something like this:

            if [Max Date per Mark] = [Date]

                then [your measure here]

                else 0

            END

            3.now if you have date in the columns shelf and the calculated field from step 2 as the label, this should behave as you desire.

             

            Please comment if it works so others can use this solution!

             

            Imran