1 Reply Latest reply on Apr 9, 2014 2:21 AM by Jim Wahl

    Calculate values for multiple date ranges in single view

    Devin Brown

      Hi, this seems like it should be relatively easy, but I can't seem to find any answers by searching here or the web. Basically I am simply trying to show sum totals of a measure for several different date ranges in a single view. For instance, stores would be in the rows shelve, total sales would be on the marks shelf, and on the columns shelf I would want to see the current week, period to date, quarter to date, and year to date. All of these are fiscal dates, not calendar, which I usually join using a crosswalk table between actual date and the fiscal week/period/quarter/year it sits in.


      Unfortunately, I can't seem to figure out how to display each of these date ranges within the same view. Is there a trick to this?

        • 1. Re: Calculate values for multiple date ranges in single view
          Jim Wahl

          One way to do this is to create a measure for each time period:

          Sales Week to Date =

          IIF((DATETRUNC('week', [Order Date]) == DATETRUNC('week', #2013-04-09#)) AND

              ([Order Date] <= #2013-04-09#), [Sales], NULL)


          Sales Month to Date = ...


          (Replace #2013-04-09# with TODAY() or an parameter-based option. I often create a calculated field Today = IIF([Use Custom Date], [Custom Date], TODAY()), where Use custom date and Custom Date are both user-specified parameters.)


          Also, your formula will be a little different you're using FY and a date table.


          Then you can add these Measure Names / Measure Values to the Columns shelf.


          Note that these measures will be calculated across the entire data set. If you have 10 years of data, consider adding a date filter so that only the relevant rows are included in the worksheet. If you're only looking at YTD max, then add a date filter that includes all dates within the last year.