Sum multiple rows of a Matrix by summing all months up to the previous month

[jof4]

I am trying to convert a dashboard I made on excel onto PowerBI, however, I am a complete novice with powerBI so all help is greatly appreciated.

With excel, i used the formual =SUM(OFFSET($E$14,0,0,1,MONTH(TODAY())-1)) 

Below is a basic example of the dashboard I have (the months would run all the way to december).

In addition to the year total column, I would like an additional column which sums all the months values to the end of the previous month. In this example, it should sum from Jan to Apr. If the month was October, it shoudl sum from Jan to Sep.  

I am really struggling with this so any help would be greatly appreciated. 

Thanks!

	Jan	Feb	Mar	Apr	May	Year Total	Year to previous month total
New Business	100,000	50,000	150,000	250,000	100,000	650,000	550,000
Renewed Business	75,000	25,000	100,000	100,000	100,000	400,000	300,000
Lapsed Business	50,000	25,000	50,000	25,000	30,000	180,000	150,000
Commission	10%	5%	15%	20%	15%	13%	12.5%

[Anonymous · Accepted solution]

Hi @jof4 ,

I create a sample pbix file(see the attachment), please check if that is what you want.

Year to previous month total =
VAR _maxmonth =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )
VAR _pmtotal =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            'Table',
            'Table'[Date] >= DATE ( 2023, 1, 1 )
                && 'Table'[Date] <= EOMONTH ( _maxmonth, -1 ) //EOMONTH ( TODAY (), -1 )
        )
    )
RETURN
    IF ( ISINSCOPE ( 'Table'[Date].[Month] ), SUM ( 'Table'[Value] ), _pmtotal )

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards