locked
Calculating a measure between two dates RRS feed

  • Question

  • Hi,

    for a pivot table I need to create a measure respect of the production date between FromDate and ToDate specified as report filters.

    Is it possible, please? An example? Thanks

    Monday, May 23, 2011 3:26 PM

Answers

  • I've solved by using two unrelated Time tables, the first one for the FromDate filter and the second one for the ToDate filter.

    Many thanks

    • Marked as answer by pscorca Tuesday, May 24, 2011 12:58 PM
    Tuesday, May 24, 2011 12:58 PM

All replies

  • Hmm.. I can't think of a meaningful measure. The only thing I can think of is DatesBetween but it takes a single date column, not two.
    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, May 23, 2011 8:59 PM
  • You need to be more specific about your model and the exact calculation you want to perform. In a simple case where there is a Sales table related to a DimDate table and another unrelated table from which you can select FromDate and ToDate, if you have a measure [m1] defined as

            Sum(Sales[Amount])

    then measure [m2] defined as

            [m1](DimDate[Date] >= Values('Table'[FromDate]), DimDate[Date] <= Values('Table'[ToDate]))

    would return the sum of sales amount between the two given dates.

    Again you need to describe your questions in more details so people on this forum don't have to guess what you are trying to achieve.

    Tuesday, May 24, 2011 1:42 AM
  • Hi, sorry but I'm new with PowerPivot but I don't think it is so hard to calculate a measure by filtering between two dates. It should be a classic query over a dwh or a cube.

    Suppose to have Sales as a fact table and Amount as a measure. In the Sales fact table I've a production date (or, if it is simpler, the order date). Moreover, I've a Time as a temporal dimension. I've imported the fact table and Time dimension. Time has a DateKey as a primary key. I have created the relationship between Sales and Time by linking the production date of Sales with Date of Time dimension. In Time dimension I've created a calculated column named ToDate: it is equal to Date key of Time. I've put Date and ToDate from Time dimension as two report filters (no slicers). When a user select a value from Date filter (it is the FromDate) and a value from ToDate filter (it is the ToDate) I need to calculate the sum of Amount about the sales rows with production date between Date and ToDate.

    I think this is a classic scenario also for working with PowerPivot, or not? Is it possible to solve this issue, please?

    Many thanks :-)

    Tuesday, May 24, 2011 6:41 AM
  • I'm trying to do this formula:

    CALCULATE(SUM(TblFact_Orders[Quantity]), FILTER(Time, Time[Date] >= VALUES(FilteringTime[From_Date]) && Time[Date] <= VALUES(FilteringTime[To_Date])))

    but it functions only when I select the same value for From_Date and To_Date.

    TblFact_Orders is related to Time by ProductionDate (of the fact table) and Date (of Time).

    FilteringTime is a unrelated table.

    Any helps to me, please? Thanks :-)

    Tuesday, May 24, 2011 10:00 AM
  • I've solved by using two unrelated Time tables, the first one for the FromDate filter and the second one for the ToDate filter.

    Many thanks

    • Marked as answer by pscorca Tuesday, May 24, 2011 12:58 PM
    Tuesday, May 24, 2011 12:58 PM