Home Forums Power Pivot Filter CALCULATE by date range?

This topic contains 2 replies, has 2 voices, and was last updated by  LoadedGloves 2 years, 4 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
• Author
Posts
• #5933

Participant
• Started: 1
• Replies: 1
• Total: 2

Hi guys, quick question:

So, I’m trying to analyze for New Customers.I have data loaded into the model.

I try to write a CALCULATE measure to filter for First Order Date between “2/1/2015” and “3/1/2015” .  I’m trying to see new customers for Feb-2015.  Clearly, my logic’s wrong because the pivot returns blank.  So, all the rows are being filtered out before returning rows?  I am trying to learn the logic.

I have a measure for Total Sales.

I wrote =CALCULATE([Total Sales],
FILTER(VALUES(Data),Data[FirstOrderDate]>=2/1/2015 && Data[FirstOrderDate]<3/1/2015))

I attached a sample file.

So in summary, I thought this would filter Feb-2015 New Customers.  I’m just trying to filter for “new customers” using the FirstOrderDate column.  Next step would be, ok what did you order in the next 12 months (using the next column called “OriginalOrderDate”)

For what it’s worth, I have a CalendarTable relationship, but not sure it’s relevant for this case

###### Attachments:
You must be logged in to view attached files.
#5947

Jerid421
Participant
• Started: 1
• Replies: 13
• Total: 14

Hey LG,

Your calendar table (what I call dim_Date) is certainly relevant, as the time-intelligence functions in DAX are hugely powerful.  Every data model should have one.

There are a couple of ways to skin this cat.  Here is one:

• First, I would rename your calendar table to dim_Calendar.
• I would then create a calculated column in that table called New_Feb with a formula like: =IF(DATESBETWEEN(Data[FirstOrderDate], DATE(2015,2,1), DATE(2015,3,1)), TRUE, FALSE).
• You can then create a measure in the Data table like this:  New_Feb_Sales: = CALCULATE([TotalSales], dim_Calendar[New_Feb] = TRUE())

I think that you can also do what you were trying by simplifying your formula to something like this:

• ```=CALCULATE(SUM([TotalSales]), DATESBETWEEN(dim_Calendar[yourdatefield],
DATE(2015,2,1),
DATE(2015,3,1)
))```

Hope this helps.  Check the syntax on my formulas as I didn’t test them.

Thanks,

Jerid

#5980

Participant
• Started: 1
• Replies: 1
• Total: 2

Hi, thanks for your input.  I especially found your DATE() function interesting. Makes sense.  Feedback was helpful.

And, thanks for the illustration using your CALCULATE measure.  Very enlightening.

So, unfortunately, on your =IF(DATESBETWEEN…) it threw an #error. I thought it would work too because it accepts the created relationship.

I tried using a RELATED function, but it didn’t like that either

=IF(DATESBETWEEN(RELATED(DATA[FirstOrderDate]),DATE(2015,2,1),DATE(2015,3,1)),TRUE,FALSE)

Any explanation why the Related still didn’t return a Feb calculated column?  Error:  DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.