Skip to Content

Prior Year Cumulative measure giving me Actual year Amount - Help

I get Actual Amount for Prior Year Selected. Please see attached files for my Output, Queries, Measures used and selection. THanks for all you help. I need to make Cumulative - Prior Year Balance GL measure show as Prior year amount but its giving me the same output as Actual Year

Output.jpg (181.4 kB)
query 2.jpg (86.8 kB)
query 1.jpg (90.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • May 25, 2015 at 01:41 AM


    Create a new varaible Cumulative Prior Year = previous([Cumulative - Actual Year]) and use this in thae table.



    Add comment
    10|10000 characters needed characters exceeded

    • Yes. It is the actual amount measure to be used in place of [Amount].

      For [Prev Year]= ToNumber(UserResponse("Enter a Year");" ") -1, just create a variable using this formula and verify whether you are able to fetch prev year.Merge year objects. Use this prev year variable in the calculation of amount variable and check whether you are getting expected results for prev year.



  • avatar image
    Former Member
    May 26, 2015 at 09:57 AM

    Have a look at the SQL generated and see if the measure value that is causing you problems would logically return previous year or current year transactions. If you have any doubts, raise them with the universe designer as they will know your objects better than we do.

    The construct for a prior year object should be a case statement along the lines of (in pseudo code terms):

    sum(case when year(transaction_date) = year(currentdate)-1 then transaction_value else 0 end)

    The other way to achieve this is through a smart calendar table, such that the calendar dimension gets updated each night to denote certain things - last week flag, yesterday flag, etc In your case, there would be a prior year to date 1/0 flag and prior year to date would simply be:

    sum(transaction_value * prior ytd flag)

    Add comment
    10|10000 characters needed characters exceeded