Forum Discussion

wallingfordce's avatar
wallingfordce
Data Pipeline
03-23-2022

Stopping an RSUM after its last value

When using RSUM over a category, and when the RSUM has underlying values only for some early periods of time, and not later periods, it sums in the NULLs from those future periods showing values exte...
dashboards & reporting
harikm007's avatar
harikm007
Data Warehouse
03-24-2022

wallingfordce ,

Please try this formula if that gives you expected result. (use column with 0 instead of NULL)

 

case when [Total this year] = 0 then null else Rsum([Total this year]) end

 

 

If you are using column having NULL instead of 0, try this:

 

-Hari

 

wallingfordce's avatar
wallingfordce
Data Pipeline
03-24-2022

Thanks Hari, I have great appreciation for your posts.

In this "case," it would need to be more intricate. There could be valid zeros in the series. This is the formula I was using, but it's a little expensive and I was hoping to simplify...

  • if the rsum for the selected FY at this point in the curve is < the total for the widget for the selected FY , then we're not at the end of the curve
  • if the rsum for the selected FY at this point in the curve is = the total for the widget for the selected FY , then we might be at the last point or past the end of the curve...
  • if the ticket count in at this point is > 0 then we're at the last point on the curve
  • else we're past the last point on the curve
  • if we not past the end of the curve, show the rsum value
  • else show null
Displayed as the "Alt" values in the screenshot.
IF (
RSUM( ( [Total Count] , [Fiscal Year] ) ) < ( [Total Count] , [Fiscal Year] , ALL( [Calendar Month Name]) )
  OR (
  RSUM( ( [Total Count] , [Fiscal Year] ) ) = ( [Total Count] , [Fiscal Year] , ALL( [Calendar Month Name]) )
  AND
  ( [Total Count] , [Fiscal Year] ) > 0
    )
  , RSUM( ( [Total Count] , [Fiscal Year] ) )
  , NULL
)