Forum Discussion

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
nandhinielango
Frequent Visitor
8 years ago

This gives me the same result as cumulative values.

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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 )
            )
    )