Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try the below
Sum(total <Date> Balance)
or
Aggr(NODISTINCT Sum(Balance), Date)
Please mark closed ,if it resolved the issue.
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.