Filter - Measure Problem

CSMcVey

New Member
Joined
Oct 30, 2012
Messages
21
I am trying to create a measure that returns a total for actions that took place in the last 60 days, the current measure is: ActionsInLast60Days = CALCULATE([ActionTotal], FILTER(DateMatrix, DateMatrix[Date] > [Date60DaysInPast]))

This is not working, it is not filtering anything - meaning when I add it to a table as a value and also add the Measure ActionTotal, the values are the same; it's not taking only items in the last 60 days

With some testing I created this measure: ActionsIn2013 = =CALCULATE([ActionTotal], FILTER(DateMatrix, DateMatrix[Year] = 2013))

When I add this as a value to the testing table it works perfectly, only includes items that took place in 2013.

I for the life of can't figure out what I am doing. I am restricted to PP version 1. I have a 'Data tab'e called Date Matrix, it has a relationship to my fact table on the date fields. Any ideas?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
try something like
Code:
Name:= CALCULATE( [COLOR=#574123][ActionTotal], 
[/COLOR]            DATESBETWEEN(  dates table here, 
                          FIRSDATE( DATEADD( LASTDATE( dates table here again), -60, DAY) ),
                          LASTDATE(dates table here once again)
                         )
            )

That's taking in consideration the last 60 days counting from the last date that you've chosen from your slicers. So if you have multiple dates chosen, it will only take the very last one to start counting the last 60 days.

Hope this helps,
Miguel
 
Upvote 0
Worked perfectly, still a noob with PP. This was my first exposure to really using and understanding DatesBetween. Another item for the toolset. Many thanks.
 
Upvote 0
I have an alternative approach that works much like the [Actionsin2013] measure in the OP.

I basically add a calculated column to the calendar table which applies a status of 'L60D' to each of the last completed 60 days:

Code:
[L60D]=IF('DateMatrix'[Date] >= TODAY()-61 && 'DateMatrix'[Date] < TODAY(),
       "Y",
        BLANK()
        )

From here its pretty simple:

Code:
[ActionsinL60D]=CALCULATE([ActionTotal], DateMatrix[L60D] = "Y")

Its clearly not as 'smart' as Miguel's solution but my experience (and a fair bit of testing) says that this will run much faster than anything involving DATESBETWEEN / DATEADD once you get to a fact table of 5m+ rows as well as being simpler to write and understand. A further slight performance improvement could be gained if you do the dynamic 60 days thing as part of the refresh so its 'hard coded' in the calendar table.

Obviously this is only going to be practical if the whole 'last 60 days' thing is going to be used regularly and you don't have a million other variants you need to set up.

Jacob

ps You didn't need the FILTER() in [Actionsin2013]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top