Home Forums Power Pivot "invalid numeric representation of date value" error

This topic contains 6 replies, has 2 voices, and was last updated by  tomallan 9 months ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #9132

    adm
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    Hi,

    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-

    1. 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!

     

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

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Interesting.

    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.

    Tom

    #9149

    adm
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    Sure. I will do that soon, mostly on Monday.

    Thank you.

    #9175

    adm
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    Hi Tom, Attached is the sample data. It also has formulas in one sheet.

     

     

     

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

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    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.

    Tom

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

    adm
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    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.

    A

    #9191

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    A,

    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.

    Best regards,

    Tom

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

You must be logged in to reply to this topic.