Account open balance on every row of month end data.

[ali22026]

Hello every one,

This the my raw data i want to reflect the the first value of open date to whole year of report date
My result table should be 
OpenDate , ReportDate,  BaseBal, Balance

1/31/2022,   1/31/2022,   1790,     1790

1/31/2022,   2/28/2022,   1790,     1563

1/31/2022,  3/31/2022,    1790,     1636
  

Note: this i want to do in expression on this more calculations are depended

Thanks in advance.

[Lakshmanan]

Try the below 

Sum(total <Date> Balance)

or

Aggr(NODISTINCT Sum(Balance), Date)

Please mark closed ,if it resolved the issue.

[barnabyd]

G'day @ali22026 ,

I would do this calculation in the load script. Something like this ...

Account:
load * inline
[   OpenDate,  ReportDate,  BaseBal_raw, Balance
    1/31/2022, 1/31/2022,   1790,     1790
    1/31/2022, 2/28/2022,   0,     1563
    1/31/2022, 3/31/2022,   0,     1636
];

MonthBaseBal:
left join (Account)
     load OpenDate
        , BaseBal_raw as BaseBal 
 resident Account
    where BaseBal_raw <> 0
;

This code is a bit rough, so you might need to debug it to get the left join working correctly but this should give you your required result in the data model so you can use it everywhere.

Cheers, Banraby.