Sum of column grouped by month

[Lenin]

Hi,

    I have a table and a bar chart (see below). For each month in the bar chart I want to apply the following algorithm

if
[Status] is "Open"
and
[CreatedDate] before end of the previous month
then
sum [Exposure] till end of previous month

The intention is to provide a running total of [Exposure] by Month.

I tried creating a calculate expression:

Last Day of Previous Month Exposure = CALCULATE(SUM(Risks[Exposure]),Risks[CreatedDate] < [Last Date of Previous Month],Risks[Status]="Open")

I am unclear if a measure is the way to solve this. Sorry for newbie question.

CreatedDate	Exposure	ModifiedDate	Status
19/02/2018	10	19/02/2018	Open
19/12/2017	9	06/02/2018	Open
25/01/2018	9	25/01/2018	Open
19/12/2017	20	27/12/2017	Closed
19/12/2017	15	27/12/2017	Closed
19/02/2018	15	19/02/2018	Open
19/12/2017	16	28/12/2017	Closed
09/01/2018	20	12/02/2018	Open
19/02/2018	10	19/02/2018	Open
19/12/2017	9	06/02/2018	Open

Many thanks

[v-chuncz-msft · Accepted solution]

@Lenin,

You may take a look at link below.

http://community.powerbi.com/t5/Desktop/Cummulative-sum-by-month-and-year/m-p/365277#M165160

[Lenin]

v-chuncz-msft thanks a million it worked first time. I ended up with

Last Day of Previous Month Exposure =
VAR d =
[Last Date of Previous Month]
RETURN
CALCULATE (
SUM([Exposure]),
FILTER (
ALL ( 'Risks' ),
Risks[CreatedDate] <= d
&& Risks[Status]="Open"
)
)

[v-chuncz-msft]

@Lenin,

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.