3 Replies Latest reply on Apr 21, 2017 8:00 AM by Zhouyi Zhang

    How to separate date

    Tulu Sahoo

      Hi All,

      As per the below image, I have latest month calculated field and Latest year calculated field. So based on the parameter1 I want to show the values I mean, when will select latest month on parameter1 then sheet1 should give latest month data and like for Latest year. When will select Other in parameter1 then I want the data based on start date and end date selected on the dashboard. Kindly help me to do that. Thanks

        • 1. Re: How to separate date
          Mark Bradbourne

          DATEPART('year',[DATE])     or          YEAR([DATE])

           

          ^^^     will give you the year of the date      ^^^

           

          DATEPART('month',[DATE])

           

          ^^^     will give you the month

           

           

          You'd then set that calc to be tested in the filter, something like [DATEPARSEYEAR] = YEAR(TODAY())  <--- This would one show data for the current year

           

           

          Is that what you are trying to parse out? Let me know.

          1 of 1 people found this helpful
          • 2. Re: How to separate date
            Chris Cate

            Hey Tulu,

             

            See the attached workbook.  I believe it does what you want.  In my example I used Order Date as the date we care about, it could be any date. The key was to set up Start and End date calculations and check whether or not the Order Date falls between them then filter only on the ones that do.  Then use the parameters you showed to populate the calculated Start/End dates.

             

            Have a look at the following calculations I created in the workbook: Max. Order Date (LOD calc used for the two "Latest" options), Start Date, End Date, Latest First of Month, Latest End of Month, Latest Year Start, Latest Year End, Order Date Filter

             

            And also these parameters (same as yours pretty much): Timeframe, Start Date Param, End Date Param

             

            Let me know if you have any questions.

             

            -Chris

            • 3. Re: How to separate date
              Zhouyi Zhang

              Hi, Tulu

               

              Since you already have the parameters, not sure the start date/end date are parameters or not, if not, you can create two for them.

              my solution is something like

               

              IF [parameter1] = "Other" then

                   IF [your date column] >= [start date] and [your date column] < [end date] then

                        [your value column]

                   else

                        0

                   end

              elseif  [parameter1] = "Last month" then

                   [your last month calculation]

              elseif  [parameter1] = "last year" then

                   [your last year calculation]

              end

               

              hope this could help

               

              ZZ