Cumulative sum by the end of last month

[Dunner2020]

Hi there,

I am calculating the cumulative sum by the end of last month. I only consider those months that are gone (not the current month). I have developed the measure which looks like as follow:

Cumulative sum =

Var last_month_date = EOMONTH(EOMONTH(TODAY(),0),-1)

var CurrentDate = MAX('Table'[Actual Incident Start Time ])

Return

sumx(

filter(ALL('Outages and Interruptions'),'Outages and Interruptions'[Actual Interruption Start Time (NZST)]<=CurrentDate && CurrentDate<=last_month_date ), [sum_measure])

However, I noticed that it only calculates the value by the end of August instead of calculating by the end of September. Could anyone guide me where I made the mistake?

[v-stephen-msft]

Hi @Dunner2020 ,

It may be because your EOMONTH function is wrong. Try

Var last_month_date = EOMONTH(EOMONTH(TODAY(),0),0)

Reference: EOMONTH DAX

If I fail to answer the question, please provide some sample data, which will be very useful for me to help you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dunner2020]

@v-stephen-msft , your modified statement produces the end date of current month not the last month.

[amitchandak]

@Dunner2020 , Try like

Var last_month_date = EOMONTH(TODAY(),-1)
var CurrentDate = MAX('Table'[Actual Incident Start Time ])
Return
sumx(
filter(ALL('Outages and Interruptions'),'Outages and Interruptions'[Actual Interruption Start Time (NZST)]<=CurrentDate && 'Outages and Interruptions'[Actual Interruption Start Time (NZST)<=last_month_date ), [sum_measure])

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Dunner2020]

@amitchandak , my measure is as follow :

var CurrentDate = MAX('Outages and Interruptions'[Actual Interruption Start Time (NZST)])

Var last_month_date = EOMONTH(EOMONTH(TODAY(),0),-1)

Return

sumx(

filter(ALL('Outages and Interruptions'),'Outages and Interruptions'[Actual Interruption Start Time (NZST)]<=CurrentDate && 'Outages and Interruptions'[Actual Interruption Start Time (NZST)]<=last_month_date ),

[sum_measure])

it produces the following output:

 

As you can see that it still showing value of September and October.

[sevenhills]

a) var last_month_date = EOMONTH(TODAY(), -1)
-- gives last month date. (9/30/2020)
-- Minor, FYI, nothing is wrong in your code ... more of FYI

b) Cumulative Sum calc - Try this
Cumulative sum =
  var last_month_date = EOMONTH(TODAY(),-1)
  Return Calculate (
              SUM( [sum_measure])
         , filter(
             ALL('Outages and Interruptions'[Actual Interruption Start Time (NZST)])
             ,'Outages and Interruptions'[Actual Interruption Start Time (NZST)]
              <= last_month_date ))

My reason for changing the formula: suspecting ALL('Outages and Interruptions') is wrong
and also, A <= B and B <= C is equal to A <= C