5 Replies Latest reply on May 25, 2016 3:30 AM by ANTONIO GAZQUEZ

    How to cancel cases?

    ANTONIO GAZQUEZ

      Hello all!

       

      I guess this a more o less common question. I tried to look for answers but I didn't find any, maybe because I'm not quite sure on how to explain.

       

      I have a dashboard which refresh every week. I managed to set up everything so when you open the workbook you see the last week. For that I have these calculations:

       

      • To calculate current week and previous week:

      IF DATEPART('week',[Order Date]) = { max(DATEPART('week',[Order Date])) }

      AND DATEPART('year',[Order Date]) = { max(DATEPART('year',[Order Date])) }

      THEN "this week"

       

       

      ELSEIF DATEPART('week',[Order Date]) = { max(DATEPART('week',[Order Date])-1) }

      and DATEPART('year',[Order Date]) = { max(DATEPART('year',[Order Date])) }

      THEN "previous week"

      END

       

      • To calculate difference between current and previous week:

      (sum(IF DATEPART('week',[Order Date])={ max(DATEPART('week',[Order Date])) }

      and DATEPART('year',[Order Date])={ max(DATEPART('year',[Order Date])) }

      THEN [Profit] END)) -

       

       

      (sum( IF DATEPART('week',[Order Date]) = { max(DATEPART('week',[Order Date])-1) }

      and DATEPART('year',[Order Date]) = { max(DATEPART('year',[Order Date])) }

      then [Profit] END))

       

      I need to have the option to come back to results from previous weeks. For example, I'd like to have a filter or parameter with week numbers so if the user choose week number 52 the sheet with this week will give the results from week 52 and difference from week 51. Basically I need an option to cancel or 'sleep' cases, since filtering is not enough (if I filter per week the sheet with current week will show no number, I guess is still considering week 53)

       

      I've attached a workbook with superstore database so you can make an idea of what I'm struggling.

       

      Any ideas on this?

       

      Thank you!

        • 1. Re: How to cancel cases?
          ANTONIO GAZQUEZ

          Here is the workbook. Sorry, I wasn't able to attach in the first message.

          • 2. Re: How to cancel cases?
            Mia Lee

            Hi, Antonio

             

            Not sure if this is what you're asking. But, I created calculated fields for each measure you'd like to see so you wouldn't need filters for your worksheets. Please let me know if this worked.

             

            -Mia-

            • 3. Re: How to cancel cases?
              ANTONIO GAZQUEZ

              Hey Mia,

               

              Thank you for your time! Maybe I didn't explain myself clearly enough. This is the situation:

               

              I have a dashboard that will refresh every time I upload a new week. Right now is showing week number 53 from 2014 because is the last week in database. If I upload week number 1 from 2015 the dashboard should show profit in this week (first worksheet), difference from previous week (second worksheet) and the graph would refresh with one more week. I think I have this part.

               

              The problem is... does anybody know the way I can go back to previous results? I'd like to have the option to come back to results from week number 52 2014 (for example) so the dashboard would show profit from week number 52, difference from week number 51 and the graph would show results just until week number 52.

               

              I tried to do this with filters but the calculation I'm using to consider last week in database seems not to work with this.

               

              Please, let me know if you need further explanation on this. Hope this helps to better understand.

               

              Thank you again!

              • 4. Re: How to cancel cases?
                Mia Lee

                Hi, Antonio

                 

                I simplified your dimensions/measures - basically,

                 

                1. I created a parameter so people can select the day. (sorry, you can't select the week, but this will cause an issue i'll explain it later if you're interested)

                2. created 3 measures to calculate that week's profit, the previous' week's profit and the difference.

                Please find the attached workbook and let me know if this helped or you have other questions.

                 

                -Mia-

                1 of 1 people found this helpful
                • 5. Re: How to cancel cases?
                  ANTONIO GAZQUEZ

                  Hi Mia,

                   

                  It worked! I managed to adapt the calculations to my workbook. It's not what I was looking for initially but you found another way to solve it.

                   

                  Thank you!