Calculating a measure between two filtered dates

# Calculating a measure between two filtered dates

• Friday, April 27, 2012 12:09 PM

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

### All Replies

• Friday, April 27, 2012 12:15 PM

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

• Saturday, April 28, 2012 9:13 AM

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

• Tuesday, May 01, 2012 9:14 PM

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 Tuesday, May 01, 2012 9:14 PM correction
•
• Wednesday, May 02, 2012 8:06 AM

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

• Thursday, May 03, 2012 8:56 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.
• Monday, May 21, 2012 3:00 PM

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:56 PM