2 Replies Latest reply on Feb 14, 2018 3:46 PM by Shinichiro Murakami

    Assigning Date range using the dates

    Mohammed Junedi

      Hi, Everyone,

      I have a requirement to create an report based on certain date range which are part of the report.

      1. Transaction date

      2. FIscal_Month_Start_date : every month 5th

      3. Fiscal_month_end_date. every month 4th

       

            

      Transaction_dateFIscal_Month_Start_dateFiscal_month_end_dateFiscal MonthSalesDivision
      1/6/20181/5/20182/4/2018Feb300.00North
      1/20/20181/5/20182/4/2018Feb100.00East
      1/28/20181/5/20182/4/2018Feb50.00west
      1/30/20181/5/20182/4/2018Feb200.00South
      1/31/20181/5/20182/4/2018Feb500.00south
      2/1/20181/5/20182/4/2018Feb250.00East
      2/3/20181/5/20182/4/2018Feb100.00west
      2/4/20181/5/20182/4/2018Feb230.00West
      2/5/20182/5/20183/4/2018March13.00North
      2/6/20182/5/20183/4/2018April14.00North
      2/6/20182/5/20183/4/2018May15.00South

       

      I need to develop the report to show all the transactions happened based on Fiscal Month to date which are based on fiscal month start and End date. I created 2 formulas to capture the dates. 

      for example:

      Fiscal_month_start_Date = '2/5/2018'   and Fiscal_Month_end_date = '3/4/2018'

       

      First vairable : FYMTD Start :  (if [ChatDate] = TODAY()-1 then [Fiscal_month_start_Date]  end)

      Second Vairable : FYMTD End : (if [ChatDate] = TODAY()-1 then [Fiscal_Month_end_date]  end)

       

      Date range : [Transaction date] > = [FYMTD Start]  and [Transaction date] <= [FYMTD End]

       

      When i use the this date range in filter as true i am getting only Yesterday date ( 2/6/2018) . not the date range ( 2/5/2018 till 3/4/2018) for transaction date. Please help me how to achieve this.

       

      Thanks in advance.