Calculating a measure between two filtered dates

# Calculating a measure between two filtered dates

• 27 เมษายน 2555 12:09

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

### ตอบทั้งหมด

• 27 เมษายน 2555 12:15

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

• 28 เมษายน 2555 9:13

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

• 1 พฤษภาคม 2555 21:14

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

• แก้ไขโดย 1 พฤษภาคม 2555 21:14 correction
•
• 2 พฤษภาคม 2555 8:06

hi, you can also ask your query on the given forum. it may hep you.

• 3 พฤษภาคม 2555 8:56

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.
• 21 พฤษภาคม 2555 15:00

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

• 21 พฤษภาคม 2555 15:56

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.