Opening Balance

[tommyl]

Hello experts,

I am trying to create a balance report. I need to state the opening balance as a row. In the chart expression i wrote: 

RangeSum(Sum([amount]),-Above(Sum({<[createdat.autoCalendar.Month], [createdat.autoCalendar.Year]>}fabs([amount])))
) *
Avg(1) 

I have Month and Year dimensions in the pivot table as attached:

Despite before 2019 December there is no transaction, the opening balance is not zero.

Could you please help me on that?

Regards,

Tommy.

[tommyl]

Hello,

i also tried this too:

Sum({$<[createdat]={"$(=Date(AddMonths(Max([createdat]),-1),'YYYY-MM'))"}>}[amount])

This didnot work either. 

[tommyl]

I also tried this: 

rangesum(Above( sum(amount]) , 1, rowno()))

but it works only within the month not in the "opening balance" row and i can only see this when i add createdat as a dimension to the table. .  

And tried this:

RangeSum(Above(Total Sum({<[createdat.autoCalendar.Year]=,[createdat.autoCalendar.Month]=>}([amount])),0,RowNo(TOTAL))) * Avg(1) but first value does not turn out to be zero:

Any guide/clue/help really appreciated. 

[sunny_talwar]

What exactly are you hoping to get?

[Gabriel · Accepted solution]

Hi,

wrote a blog post a while back and I think it might help you. See link below

Opening balance 

[tommyl]

Hello Sunny,

I want to derive below content in different format:

What i am trying to display is: 

Columns in the rows and rows in the columns. 

Is there a way?

Thank you,

T. 

[tommyl]

Hello Gabriel,

I changed the display of the pivot since your Cartesian problem in the blog directed me so. 

It worked thank you. 

Thank you,

Tommy

[Gabriel]

Hi,

Am glad it worked for you.