8 Replies Latest reply: Jun 12, 2018 6:08 AM by omar bensalem RSS

    How to calculate fiscal period sales to date, similar to MTD?

    Peggy Wells

      I have sales by invoice date in my model.  For every line item, i have the invoice date, fiscal period work day number, period days,and many other attributes.  I need help with calculating accumulated sales by day. 

       

      This is the formula that I started.  I'm also showing the sample table and results desierd.  I am intending to add a combo chart showing the accumulated sales by day in a fiscal period.  I appreciate you help with fixing this formula.

       

      Sum({$<YEAR_PER={'2018_001',PER_WD={">=0 <=$(=Num(Max(PER_WD)))"}>}SALES)

       

      Example Table: 

      YEAR_PERINV_DATEPER_WDPER_DAYSSALES
      2018_0011/1/201802410
      2018_0011/2/2018124100
      2018_0011/3/2018224100
      2018_0011/4/2018324100

       

      Mock Results needed:

        

      PER_WDPER_To_Date
      010
      1110
      2210
      3310