There is something strange going on with the dates in the DATESBETWEEN function in this lesson. I have Excel 2016 - Office 365. I am in the US so our dates are MM/DD/YYYY.
I have my own version of this workbook where I deleted all measures and started from scratch as I am working through the lessons.
- In the sample file (2013) version, the measure is =CALCULATE([Total Orders $], DATESBETWEEN(Dates[Date], "5/4/2009", "4/4/2010")). For me, that is May 4, 2009 through April 4, 2009. However, it is calculating April 5, 2009 through April 4, 2010 and giving the answer $3,732,824.
- When I created the measure, I keyed in =CALCULATE(Orders[Total Orders $],DATESBETWEEN(Dates[Date],"4/5/2009","4/4/2010")), which is April 5, 2009-April 4, 2010. But it gives the answer $3,365,055, which is the total of values from May 4, 2009-April 4, 2009.
- I changed the measure to =CALCULATE(Orders[Total Orders $],DATESBETWEEN(Dates[Date],date(2009,4,5),date(2010,4,4))) and that calculated the correct $3,732,824.
- I did a small test with the following measure =CALCULATE(Orders[Total Orders $],DATESBETWEEN(Dates[Date],"1/1/2009","1/19/2009")) which for me is Jan 1 2009-Jan 19 2009, and it correctly returned $323,667. So I know Excel is seeing the MM/DD/YYYY format.
It seems if the way to view it could be ambiguious, as in #1 above (the US sees the start date as May 4, 2009, but AUS sees it as April 5, 2009), then it assumes DD/MM/YYYY format, but in #4, if it cannot be read any other way (No such date as 1/19/2009 in AUS), then it reads it as the US sees it - MM/DD/YYYY.
So anyone know what is going on?
FWIW, I am on Windows 10, and my region settings are - Short Date M/D/YYYY, so 5/4/2009 should be seen as May 4, 2009, not April 5, 2009, yet PowerPivot isn't doing that.
Hi Ed,
Did you change the date format for the Date column of the Dates table to m/d/y? In my file I have set the data type to Date and the date format is dd/mm/yyyy.
Mynda
I didn't change it, but the date column in the pivot table is showing M/DD/YYYY, and the [Dates] field in the Dates table is also showing M/DD/YYYY.
I created my own file as a test and it worked using the "m/d/yyyy" syntax as well as with the date(yyyy,m,d) formula.
There is something odd in how your file is working here in the US. If it was purely in the d/m/y format, I would have expected my test above using 1/19/2009 to blow up since there is no month 19, but that works fine.
I think the lesson for me is to use the date() formula so no matter what, I know how the dates will be processed. I almost always use that in Excel functions as well, so it is no big deal. Still a bit puzzling.
In Excel when I import a file containing dates formatted as mm/dd/yyyy, let's say it's a text file, it will try to use my default date format (dd/mm/yyyy) to interpret the dates. You can imagine this ends up in a mess because when it finds a date entered as 1/19/2010 it will either import it as text, or it will switch it to 19/1/2010. This is the default behaviour, so I'm not surprised that Power Pivot is doing the same thing.
There is obviously some formatting information left in my file that you haven't changed that is being used by the Power Pivot calc engine. Probably the date format setting I mentioned earlier. It sounds like the PivotTable is using the PC region settings to determine how to display the dates in the worksheet, likewise the date column you see in the table, but in the background the calc engine is using the setting applied to the column/field.
Thanks Ehans - just stumbled over the same thing and found this very helpful
When I did a DATESBETWEEN for the following range april 4th 2009 thru may 4th 2009 written as 4/4/2009 thru 5/4/2009 the displayed result was 7,613 and checked what should be the answer it was 408,497!
