+ Reply to Thread
Results 1 to 11 of 11

Power pivot relationship for date not working

  1. #1
    Registered User
    Join Date
    07-19-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Power pivot relationship for date not working

    I have the following tables
    MonthlyBalances
    AllBudgets
    Scenario
    Calendar

    MonthlyBalances and AllBudgets are linked to the Calendar table via a DateKey - see picture attached

    I've tried to recreate the scenario as per excelcraft . com Actual vs Budget but it keeps asking me for a relationship and doesn't show the correct data when using columns from the Calendar table as a filter

    Any ideas what is wrong with these relationships?

    The pivot uses
    [ReportGroup].[Group Name]
    [ReportGroup].Actual vs Budget

    This works fine until I add the Slicer for from the Calendar
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by AliAB; 07-19-2017 at 05:52 PM. Reason: add file

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power pirvot relationship for date not working

    Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,885

    Re: Power pirvot relationship for date not working

    Does your calendar table have dates from 1/1/year to 12/31/year (i.e. all dates in year for range of year in the table)?

    If not, this can cause issue when some functions are used in DAX (many of date period related functions) and may result in unexpected result.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    07-19-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Power pirvot relationship for date not working

    I've added the file. The pivot seems to work but it comes up with the RELATIONSHIP MAY BE NEEDED MESSAGE when the slicer is created on the Date

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power pirvot relationship for date not working

    no file attached

  6. #6
    Registered User
    Join Date
    07-19-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Power pirvot relationship for date not working

    hopefully should be now

  7. #7
    Registered User
    Join Date
    07-19-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Power pirvot relationship for date not working

    Yes the Calendar Table has all the dates.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power pivot relationship for date not working

    Ok, whats now? I tried slicer and all works (Aug, Jul, Jun - 2018)

    What is wrong - in details?

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power pivot relationship for date not working

    maybe it will help: PPRelationship warning
    or
    here: Relationship May be Needed
    Last edited by sandy666; 07-19-2017 at 10:05 PM.

  10. #10
    Registered User
    Join Date
    07-19-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Power pivot relationship for date not working

    Thank you!

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power pivot relationship for date not working

    thank you means: solved?

    If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Powerpivot relationship not working
    By Bikashmittle in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-22-2017, 11:12 AM
  2. Replies: 0
    Last Post: 02-16-2016, 05:34 AM
  3. Replies: 0
    Last Post: 11-17-2015, 03:12 PM
  4. Replies: 0
    Last Post: 07-28-2015, 05:35 PM
  5. Unable to create meaningful relationship in Power Pivot
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-08-2014, 10:28 AM
  6. PowerPivot many to many date relationship
    By chaserracer83 in forum Excel General
    Replies: 1
    Last Post: 10-08-2012, 03:48 PM
  7. [SOLVED] Many to many date relationship
    By chaserracer83 in forum PowerPoint Formatting & General
    Replies: 2
    Last Post: 10-08-2012, 02:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1