5 Replies Latest reply on Jun 12, 2018 12:00 PM by Deepak Rai

    Calculate Amount between periods

    Megan Mooney

      Hello,

       

      I am trying to calculate the total amount between periods using date parameters. I am using a LOD expression, but I can't figure out how to get it to work with the dates factored in as well as the counterparty. I need to calculate a weighted average by period so I need the 'Tot Amt' column to only sum the current year selected in the date parameter. I think I need to be using a table calculation instead of a LOD, but I am not sure how.

       

      Please help.. I attached a sample workbook.

       

       

      IF DATEPART('year',[Filtered Date]) >=DATEPART('year',[Start Date])  AND  DATEPART('year',[Filtered Date]) <=DATEPART('year',[End Date])

      THEN  { FIXED [Counterparty],DATEPART('year', [Filtered Date]): SUM([Amount]) } END

       

      When I add, the DATEPART to the LOD - it creates different sums by year. I want it to sum for the entire period.

       

      Thanks!