Getting monthly figure from cumulative figure

[dadelta]

My report is broken down into subtotals.  I created a measure to get the total.  My DAX for the total looks like this:

  Line 17 = SUMX('1257csv',[Cell050] + [Cell051] + [Cell052])  (Cell050 etc are the column names)

Unfortunately, this infomation is reported as a cumulative figure each month over the course of our fiscal year. 

Our FY runs from Oct 1 through Sep 30.  What I need to do is use the Oct figure (this would be a 1 month figure) and then subtract the prior month from the current month from Nov through Sep.  The totals would reset each October.

My data table looks like the first table below.  What I need looks like the second table below. 

I am very new to DAX and have no idea how to figure this one out.

Any assistance or direction will be greatly appreciated.  Thank you.

 

Current		Needed
RptDt	Line 17		RptDt	Line 17
9/30/2017	6,745		9/30/2017	6,745
10/31/2017	909		10/31/2017	909
11/30/2017	1,821		11/30/2017	912
12/31/2017	3,065		12/31/2017	1,244

[v-piga-msft · Accepted solution]

Hi @dadelta,

For your scenario, you could create an index column in Query Editor. 

Then create the measure below.

Measure =
VAR a =
    CALCULATE (
        [Line 17_],
        FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = MAX ( 'Table1'[Index] ) - 1 )
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table1'[Rpt Dt] ) ) = 10,
        'Table1'[Line 17_],
        [Line 17_] - a
    )

Best Regard,

Cherry

[v-piga-msft · Accepted solution]

Hi @dadelta,

For your scenario, you could create an index column in Query Editor. 

Then create the measure below.

Measure =
VAR a =
    CALCULATE (
        [Line 17_],
        FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = MAX ( 'Table1'[Index] ) - 1 )
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table1'[Rpt Dt] ) ) = 10,
        'Table1'[Line 17_],
        [Line 17_] - a
    )

Best Regard,

Cherry

[dadelta · Accepted solution]

Thank you so much for you assistance Cherry.  This solution works perfectly for what I was trying to accomplish.