Subtraction from different rows

[nandhinielango]

 Hi All, 

I need to calculate the following in incremental pattern.

Period	Value (cumulative)	Value (incremental)	Remarks
Apr-17	100	100	For the start of each FY, meaning Apr of every year, the incremental value= value (Cumulative)
May-17	250	150	For subsequent months, use vaIue of that month and mius off value of previous month
Jun-17	400	150
Jul-17	600	200
Aug-17	900	300
Sep-17	1800	900
Oct-17	2000	200
Nov-17	2500	500
Dec-17	2600	100
Jan-18	3000	400
Feb-18	3200	200
Mar-18	3200	0
Apr-19	140	140

Now the value in data source is cumulative.To get incremental value:

• Value of Jun 2017 refers to value from 2017 Apr 2017 to Jun 2017. In sample below value is 400. This is Q1 value
• Value of Sep 2017 refers to value from Apr 2017 to Sep 2017. In sample below value is 1800.  To get Q2 value, use 1800-400 = 1400
• Value of Dec 2017 refers to value from Apr 2017 to Dec 2017. In sample below value is 2600.  To get Q3 value, use 2600-1800 = 800.

The data in Power BI is in this format given below. Fiscal Year, Fiscal Month and Fiscal Quarter are all text.

[Phil_Seamark]

Hi @nandhinielango

Are you simply after a Financial YTD that resets every April?

If so the following calculation might be close. It does rely that your [Period] column is DateTime, rather than Text.  Ideally this will be the first day in each month, so Apr17 will actually be 2017-04-01

Value Cumulative = TOTALYTD(SUM('Table3'[Value]),'Table3'[Period],"30/3")

[Phil_Seamark]

Hi @nandhinielango

This formula is one way  you can derive the gap backwards

Subtract Gaps = 
		[Value Cumulative] -
		CALCULATE([Value Cumulative],
			FILTER(
				ALL('Table3'[Period]),
				[Period] = var d1 = min('Table3'[Period])
						   var myStartOfMonth = DATE(year(d1),month(d1),1) - 1
						   RETURN DATE(Year(myStartOfMonth),Month(myStartOfMonth),1)
							)
						)

[nandhinielango]

This gives me the same result as cumulative values.

[Zubair_Muhammad]

Hi @nandhinielango

Try this calculated column

Incremental =
VAR CurrentLine =
    PREVIOUSMONTH ( TableName[Period] )
RETURN
    IF (
        MONTH ( TableName[Period] ) = 4,
        TableName[Value (cumulative)],
        TableName[Value (cumulative)]
            - CALCULATE (
                SUM ( TableName[Value (cumulative)] ),
                FILTER ( ALL ( TableName ), TableName[Period] = CurrentLine )
            )
    )

[Zubair_Muhammad]

@nandhinielango