Sum Previous Period with Current Period (monthly)

[Anonymous]

I found some similar posts, but I am still new to Power BI and was hoping to find a simple solution to what I am trying to do. Below is a snapshot of the data: 

What I would like to do is trend the how many claims remain open and keep a running total depending on how many were closed during the subsequent period. So for Oct it is pretty simple, created + closed = remaining open (22+(-4) = 18). Then I need to calculate Nov starting with 18 and then sum created + closed = remaining open for Nov (18 + 14 + (-20) = 12). This then needs to be calculated for every subsequent month as shown in the chart below: 

The created or opened claims is coming from one table and the closed claims are coming for another table and I am using a date table to connect the tables. 

I am looking for simple solution (or maybe I should say hoping for a simple solution), as my DAX is not as advanced as I wish it was (hopefully someday). 

Thanks for your help!

Matt

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

If your Short Month column is date type, you could use the measure below.

Measure =
CALCULATE (
    SUM ( Table1[Created] ) + SUM ( Table1[Closed] ),
    FILTER ( ALL ( 'Table1' ), 'Table1'[Short Month] <= MAX ( 'Table1'[Short Month] ) )
)

If your Short Month column is text type, you should create an index column in Query Editor and then sort the Short Month column by Index in Data view. 

Here is the fomula and the output.

Measure =
CALCULATE (
    SUM ( Table1[Created] ) + SUM ( Table1[Closed] ),
    FILTER ( ALL ( 'Table1' ), 'Table1'[Index] <= MAX ( 'Table1'[Index] ) )
)

You also could have a reference of the attachment.

Best Regards,

Cherry