Filter PowerPivot based on multiple Date Criteria

Question

I am trying to apply some Time Intelligence functions in my PowerPivot workbook concerning projects and money received for them. I have three relevant tables; Matters, Payments, and a Date Table.

Each matter has a creationDate, and a closureDate(from a linked table). Likewise, each payment has a date. I have reporting set up decently, but am now trying to use Time intelligence to filter this a bit more clearly.

How can I set a PowerPivot Pivot Table up so that the only Matters which show are those which existed within the period selected. e.g. If I select a slicer for 2014, I don't want to show a matter created in 2015, or one which was closed in 2013. The matter should have been active during the period specified.

Is this possible?

Answer 1

You want to show all the matters EXCEPT those where the CreationDate is after the upper limit of the date range you are looking at or the ClosureDate is before the lower limit of the date range you are looking at.

Assuming you have a data structure like this, where the left-hand table is the Matters and the right-hand one is the Payments:

If you have a calculated field called [Total Payments] that just adds up all the payments in the Payments table, a formula similar to this would work:-

[Payment in Range]:=IF(OR(MIN(Matters[Creation Date])>MAX('Reporting Dates'[Date]),MAX(Matters[Closure Date])<MIN('Reporting Dates'[Date])),BLANK(),[Total Payments])

Here is the result with one month selected in the timeline:

Or with one year selected in the year slicer:

NOTE: in my example, I have used a disconnected date table.

Also, you will see that the Grand Total adds up all the payments because it takes the lowest of all the creation dates and the highest of all the closure dates to determine whether to show a total payment value. If it is important that the Grand Total shows correctly, then an additional measure is required:

[Fixed Totals Payment in Range]:=IF(COUNTROWS(VALUES(Matters[Matter]))=1,[Payment in Range],SUMX(VALUES(Matters[Matter]),[Payment in Range]))

Replace the [Payment in Range] in your pivot table with this new measure and the totals will show correctly, however, this will only work if Matters[Matter] is used as one of the fields in the pivot table.

Answer 2

Use filters & the calculate function.

So, if you're Summing payments, it would look like.....

Payments 2014:= CALCULATE( SUM([Payments]), DateTable[Year]=2014)

The Sum function takes the entirety of payments & the filter function will only capture payments w/in 2014, based on the data connected to your date table.