QS: Straight Table showing Balance and Prev Year Balance vs Year.

[JustinDallas]

Hello Folks,

I have a straight table with Year, Acct# and Balance in it.  I would like to get the table to show the Balance for the Year dimension, as well as the previous year.  I'm expecting to get something like this.

I've tried doing something like this:

Sum({<Year ={'$(=Max(Year)-1)'}>} Balance)

And of course that doesn't work, because the Year dimension restricts the dataset.

Here is some test data if anyone would like a quickstart on the issue:

DummyData:
LOAD *, 
Date(Date#(AcctDateString,'MM/YYYY')) AS 'AcctDate',
DayStart(Floor(Num(Date#(AcctDateString,'MM/YYYY')))) AS %fact_calendar_key,
;
LOAD * Inline
[
'Acct#', 'AcctDateString', 'Balance'
1, '01/2019', 100
1, '02/2019', 150
3, '01/2019', 270
3, '01/2019', 345

1, '01/2020', 165
1, '02/2020', 215
3, '01/2020', 345
3, '01/2020', 345
]
;
DROP FIELD AcctDateString
;

MinMaxDates:
LOAD  
	Min(AcctDate) as MinDate,
	Max(AcctDate) as MaxDate
RESIDENT DummyData
;

LET vMinDate = peek('MinDate');
LET vMaxDate = peek('MaxDate');
DROP TABLE MinMaxDates;

MasterCalendar:
LEFT KEEP(DummyData)
LOAD 
  DayStart(Floor(Num(TempDate))) AS %fact_calendar_key,
  Dual(Year(TempDate), Year(TempDate)) AS Year,
;

Load
 	Date($(vMinDate)+(Iterno()-1)) as TempDate
 Autogenerate 1
 While Date($(vMinDate)+(Iterno()-1)) <=Date($(vMaxDate))
 ;

I would prefer to not use LY and CY flags, as those will only make sense for 2 of the years (i.e 2020, 2019) , and not any arbitrary year .

Any help is greatly appreciated, I'm not sure why I can't figure out this very, very simple problem.

[dplr-rn]

May Not be the most flexible option but did you try below approach

Dimension - Acct#

Measures

 Current Year Balance - Sum({<Year ={'$(=Max(Year))'}>} Balance)

 Previous year balance - Sum({<Year ={'$(=Max(Year)-1)'}>} Balance)

Current Year - Max(Year)

[dplr-rn]

Output

[petter]

This should work for you as the "Previous Year Balance" measure:

If( Year=Max(TOTAL Year) , Sum(TOTAL <Acct#> {1<Year={$(=Max(Year)-1)}>} Balance ) , '' )

[JustinDallas]

@petterThanks Petter.  Is there a way we can make it more robust, where it wouldn't matter whether or not the Year was the Max Year or not?  When I add 2017, the Sum doesn't show anything as would be expected by the Set Analysis.

[petter]

Sure - often these suggestions are more "proof-of-concepts" type of hacks ... I won't be able to have a second look at it until later today in 5-7 hours time I think.