Forum Discussion

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Community Champion rankCommunity 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 )
            )
    )