Calculating budget done from rolling budget left - totals issues

[123456789123456]

Hi,

I got the following data (+ a datetable), describing invoiced budgets within a month + budget left on the end of that month, by invoice. Main goal is to calculate the "budget-done" for each month.

INV	month	net	net-left	Client
INV1	01/01/2020	200	150	Client1
INV1	01/02/2020		50	Client1
INV1	01/03/2020		10	Client1
INV2	01/02/2020	100	90	Client1
INV2	01/03/2020		90	Client1
INV3	01/03/2020	20		Client1

The expected result are two matrix tables, one with summed up clientdata of delivered budget per month:

Client	01/01/2020	01/02/2020	01/03/2020	01/04/2020	01/05/2020	01/06/2020	Total
Client1	50	110	60	100	0	0	320

And one with details about the invoice

INV	net
INV1	200
INV2	100
INV3	20

I tryed a lot of stuff already, this got my the best result, but is still way off the expected result

SUM('Table'[net]) 
    - if(
        HASONEVALUE('Table'[net-left]),
        SUM('Table'[net-left])
        ,0
        )
    +CALCULATE(
        CALCULATE(
            SUM('Table'[net-left]),ALLSELECTED(dates)),PREVIOUSMONTH(dates[Date]))

Best I got so far with this measure is a Matrix displaying the invoices correctly, but all sums are wrong:

Client	01/01/2020	01/02/2020	01/03/2020	01/04/2020	01/05/2020	01/06/2020	Total
Client1	50	250	160	100	0	0	320
INV1	50	100	40	10	0	0	200
INV2	0	10	0	90	0	0	-80
INV3	0	0	20	0	0	0	20
Total	50	250	160	100	0	0	320

Thanks for your help!

[MFelix · Accepted solution]

Hi @123456789123456 ,

Try the following measure:

Measure =
SUM ( 'Table'[net] ) - SUM ( 'Table'[net-left] )
    + CALCULATE ( SUM ( 'Table'[net-left] ); DATEADD ( dates[Date]; -1; MONTH ) ) + 0

Check PBIx file attach.

[123456789123456]

Thank you for your help.

I actually had this solution at some point, but without the +0, but dropped it as it did not work with my dates table for some reason - filters wont change the visual (e.g. filtering for 2020 only -> 12 columns, instead of 2015-2025 as in my datetable), something I don't see in your pbix, need to look into that. The same issue also occurs when I use excatly your calculated datetable
Any tipps on this particular issue?

Thanks again, will mark as solved.

[MFelix]

Hi @123456789123456 ,

Be aware that I only used a sample data with information for 2020, so my calendar is limited for that time frame. Believe that the way things are setup can work with more years, however your data mockup was pretty reduced, if you want please send out a broader sample and I can check if it works properly.

[123456789123456]

I don't speak Spanish, but I think I understand it now: the way you build the data table is different from my approach (which is actually a dataflow of data to keep all reports at exactly the same level). For now I'll just adapt to your dating solution
thanks again