February 8, 2018 at 7:31 pm #9132
I have a data model where I want to calculate Sold8W (rolling 8 weeks from the latest Friday on or before today) and Sold8WLY (same period last year), the formulas used are –
Sold8W:=CALCULATE(SUM([Sold]), DATESBETWEEN(DateTime[DATEEND], [LatestWkendDate]-(8*7)+1, [LatestWkendDate]))
Sold8WLY:=CALCULATE(SUM([Sold]), DATESBETWEEN(DateTime[DATEEND], [LatestWkendDateLY]-(8*7)+1, [LatestWkendDateLY]))
Both the fact table and the DateTime table have DateEnd field values from 3/10/17 to 2/15/15. I created a pivot table to test this-
- The first problem is if I expand Jan of 2017, it automatically expands Jan of 2015 and 2016 which is annoying.
2. The formulas work perfectly well for all dates except for Feb (of all years). If I try to expand Feb of any year, it throws the error “invalid representation of date value”. This is mostly because,I think,all the dates for the date range used in DatesBetween are not available in the DateTime table. If so, i want to find a formula which checks for this and not throw the error. I tried a few variants but no success yet.
Any help is greatly appreciated.
Thank you!February 8, 2018 at 11:07 pm #9138
For first impressions, I would not use a data type name for a calendar table name. However, that may not be what is breaking your code. Could you put up some sample data in a workbook with just the Sold column and the DateEnd column from your fact table, plus your Datetime table. Also, if [LatestWkendDate] and [LatestWkendDateLY] are not columns in the Datetime table, please let me know how they are calculated.
TomFebruary 9, 2018 at 9:32 pm #9149
Sure. I will do that soon, mostly on Monday.
Thank you.February 12, 2018 at 7:33 pm #9175
Hi Tom, Attached is the sample data. It also has formulas in one sheet.February 12, 2018 at 11:39 pm #9181
Please see attached workbook. I did not have any errors, but I made a few minor changes:
I replaced MAXX with an equivalent expression using MAX. Measures which use iterators like MAXX inside of other measures or inside of a CALCULATE are an advanced topic (they have a special power called context transition that you may want to avoid for now).
Also, I did re-name the table for dates from DATETIME to Calendar. Using a data type name for a table or column name has been known to create a surprise or two.
In addition, I did some research on the exact wording of the error message, and a similar post to yours explained the DATESBETWEEN function has a sensitivity that you can avoid by writing an equivalent FILTER expression without using DATESBETWEEN. I wrote equivalent functions for Sold8W and Sold8WLY and named them Sold8W_Alternate and Sold8WLY_Alternate (I *highly recommend* that you try these alternative formulas instead of the originals).
You should also be aware that months and weeks do not align. By using calendar months on rows of your pivot instead of weeks, you will see differences between Sold8W in April of 2015 with Sold82LY in April of 2016 due to this fact.
Please see attached workbook and let me know your results.
TomFebruary 13, 2018 at 2:22 pm #9190
It is working perfectly now! I could not imagine the errors stemming from these issues aka mysteries. Thanks a lot! I will keep these in mind for the rest of my life. Thank you again.
AFebruary 13, 2018 at 2:43 pm #9191
Glad to hear of that things are working now!
Here is also good news: as you work more with PowerPivot (and also with Power BI Desktop) the mysteries will make sense, and advanced topics will change from mysteries to resources that empower your formulas and data models.
You must be logged in to reply to this topic.