Previous Month Value

[Riadh]

Hi,

I need to create monthly balance from Jan 2020 to Aug. 2020:

but in some month there is no balance because no transactions has been done.

for the month without data I want to display the previous monthly balance

Find attached data and qvf.

Thank you

[QFabian]

Hi @Riadh !, please try this example :

//you can do this data of every period in multiples ways, for example, with a For Cycle, here is just one, with values direcly in inline

Data:
LOAD * INLINE [
Date
1-1-2020
1-2-2020
1-3-2020
1-4-2020
1-5-2020
1-6-2020
1-7-2020
1-8-2020
];

//then you need to make every data crossing, using this join with all Sccount and months

join
Load distinct Account FROM [lib://desktop (fabian_fabian)/temp previous Month.xlsx]
(ooxml, embedded labels, table is Sheet1);

//then you add the existing data making null cells.

left join

LOAD
Account,
Date,
Balance
FROM [lib://desktop (fabian_fabian)/temp previous Month.xlsx]
(ooxml, embedded labels, table is Sheet1);

//finally you replace null with using peek() and previous()
Data2:
Load
if(isnull(Account), previous(Account), Account) as Account,
// Date,
monthname(Date) as Period,
Balance,
if(isnull(Balance) and Account = previous(Account), peek(Balance2), Balance) as Balance2
Resident Data
order By
Account, Date;
drop table Data;