Questions before diving in a cash management model using powerpivot and DAX

[DockS]

Hi there,

I'm refreshing/improving a cash management tool / dashboard I developed last year and I'm looking into information to do it the right way.

The environment is as follow :
- group has several companies and multiple bank account per company
- some account are checking accounts and some are managed account (assets) (and therefore need some calculation to fit in the expected output)
- output should be by year and by month (endofmonth) and total should be runing in total across years (not a simple pivot table) + get a difference from previous date (including the running parameter)

I build a model answering this with several table a query to combine them and pivot table with source = combined table but I encounter some issues :
- running total stops at each beginning of year
- difference from running total is not consistent
- model needs to refresh query and then refresh pivot table each time a record is added which is not user friendly
- output each quarter could be done without some painfull and inconsistent calculations

I'm thinking about diving a bit more into power query / powerpivot to build that model and maybe using some DAX formulas to calculate my running total and running diff and having my pivot table output.
Is it the right tool for the desired output, do you think of an alternative, is there some things to take into account before starting ?

here is an example of my actual output : https://ibb.co/tsCjMtq

I'm watching excelisfun MSPTDA serie for a start but if you have other ressources I'm all ears

Have a good day