Forum Discussion

Phil_Seamark's avatar
Phil_Seamark
Microsoft 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
    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 )
            )
    )