How do I create a measure that calculates cumulative sum of only certain rows?

[Anonymous]

I have some monthly expenditure data with multiple expenditure observations recorded per month. It looks like this:

year	month	expenditures
2020	1	4838
2020	1	3844
2020	2	1784
2020	2	2796
2020	3	2660
2020	3	3707
2020	4	4769
2020	4	1386
2020	5	4764
2020	5	4041
2020	6	1601
2020	6	3549
2020	7	1699
2020	7	2442
2020	8	1665
2020	8	2082
2020	9	2977
2020	9	3676
2020	10	1406
2020	10	3245
2020	11	4433
2020	11	3083
2020	12	2009
2020	12	2376
2021	1	2074
2021	1	3032
2021	2	4397
2021	2	3692
2021	3	1873
2021	3	2330
2021	4	1129
2021	4	4205
2021	5	1977
2021	5	3030
2021	6	4021
2021	6	1944
2021	7	1356
2021	7	3744
2021	8	2681
2021	8	3421
2021	9	0
2021	9	0
2021	10	0
2021	10	0
2021	11	0
2021	11	0
2021	12	0
2021	12	0

I want to calculate a measure that gives me the cumulative sum of expenditures by month, resetting when the year changes. I also want this measure to record no value for months that have not happened yet (i.e. September 2021 onward). For example, cumulative expenditures in February 2020 should be 13,262, cumulative expenditures in December 2020 should be 70,382, cumulative expenditures in February 2021 should be 13,195, and cumulative expenditures for September 2021 should be a missing/blank value.

How could I do this? Any help would be greatly appreciated!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below:

Measure = IF(MAX('Table'[expenditures])<>0,SUMX(FILTER(ALL('Table'),'Table'[month]<=MAX('Table'[month])&&'Table'[year]=MAX('Table'[year])),'Table'[expenditures]),BLANK())

And you will see:

For the related .pbix file,pls see attached.

[Anonymous]

Thank you so much! This works great for me. 

Out of curiosity, why do you need to make the logical statement 'MAX(Expenditures) <> 0' rather than just 'Expenditures <>0'?

[Greg_Deckler]

@Anonymous Maybe:

Column =
  SUMX(
    FILTER(
      'Table',
      [year] = EARLIER([year]) &&
        [month]<=EARLIER([month])
    ),
    [expenditures]
  )

[Anonymous]

Thanks for your response! That works for calculating the cumulative sum by month, but it doesn't give missing/blank values for the months that haven't happened yet. Do you have any ideas there?

[amitchandak]

@Anonymous , As new column

New column Cumm = if(coalesce([expenditures],0)<>0, sumx(filter(Table,table[Year]=earlier(Table[Year]). table[Month]<=earlier(Table[Month])),[expenditures]),blank())

In case you need measure and prefer to have date and join it with date table
date = date([year],[month],1)

MTD = CALCULATE(SUM(Table[expenditures]),DATESMTD('Date'[Date]))

YTD = CALCULATE(SUM(Table[expenditures]),DATESYTD('Date'[Date],"12/31"))

Final measure = if([MTD]<>0,[YTD],blank())