locked
Calculating a measure between two filtered dates RRS feed

  • Question

  • Hi,

    I'm interesting to know how to calculate a measure between two dates to filter. Fe, I've a time table and a Invoices table. Suppose to calculate the SumOfInvoices between two dates filtered.

    Any suggests to me, please? Thanks

    Friday, April 27, 2012 12:09 PM

Answers

All replies

  • If the filters are applied with slicers you can pick up the filter value using the Values function and feed this into a calculate function.  Sorry I dont have time to give you more detail.

    Lee

    Friday, April 27, 2012 12:15 PM
  • What is the table name and column name of the date column and the value of the invoice? What are the 2 dates you have in mind and are they just 2 variables. can't quite understand where these 2 dates will appear. Can you mock up the output you are looking for as quite hard to understand the question! I've just cracked doing this for a 60 day window linked to the month of a pivot table so hoping it is similar.

    Mike

    Saturday, April 28, 2012 9:13 AM
  • Hi Masplin,

    I could have an Invoices table with InvoiceID, InvoiceDate and Amount as columns. Moreover, a Time table linked to the Invoices table. I'd like to calculate the sum of Amount column of the Invoices tables between two values for the InvoiceDate, choosen from a slicer.

    Thanks


    • Edited by pscorca Tuesday, May 1, 2012 9:14 PM correction
    Tuesday, May 1, 2012 9:14 PM
  • hi, you can also ask your query on the given forum. it may hep you.

    http://stackoverflow.com/questions/1617049/calculate-the-number-of-business-days-between-two-dates

    Wednesday, May 2, 2012 8:06 AM
  • So you are looking for the start and end dates to be independent of each other and each chose from a slicer?  Could the start and end points be an day or always whole months?  I did one using a fixed 60 day window so only the end date was chosen. I'll have a think.
    Thursday, May 3, 2012 8:56 AM
  • Hi, any suggests?

    I'm trying to work with the AdventureWorksDW. I'm using the FactFinance, Amount and DateKey columns, and the DimDate.

    I've created two linked tables, the first one for DateFrom slicer and the second one for DateTo slices. Each of these slicers has a Date column. Moreover, for the DateFrom table I've created a DateFromMeasure equals to LASTDATE(DateFrom[Date]) and for the DateTo table I've created the DateToMeasure as LASTDATE(DateTo[Date]). The DimDate (datekey) table has a relationship with the FactFinance (datekey).

    Then, I've created a measure as CALCULATE(sum(FactFinance[Amount]), DATESBETWEEN(DimDate[FullDateAlternateKey], DateFrom[DateFromMeasure], DateTo[DateToMeasure])). In this way I've obtained an interesting behaviour, but I'd like to use slicers with YearTo + MonthTo and YearFrom + MonthFrom.

    Many thanks

    Monday, May 21, 2012 3:00 PM
  • Check out this article; specifically the part about disconnected slicers.

    http://www.powerpivotpro.com/2012/01/comparing-scientific-and-other-data-across-trials/

    1. Use disconnected slicer tables for the user's slicer selections.
    2. Create measures which capture the user's selection.
    3. Create an Amount measure which filters the Date dimension on the measures in step 2.
    Monday, May 21, 2012 3:56 PM