Home Forums Power Pivot date range plot

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

Viewing 15 posts - 1 through 15 (of 15 total)
  • Author
    Posts
  • #1966

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Attached is a demo file that demonstrates what I’m trying to do. The file contains two tables: one with a date range (called Items) and one is a time series table (called Sales). I added both tables to the Data Model and created a calendar table in Power Pivot to establish the relationships. I inserted a Pivot Chart that connects to the model. I’d like the plot of the Items data to be a series of dots beginning at Start Date and running through End Date. I could make a second time series table that refers to Items and does a simple check on whether the date in each row is valid for each of the Items, but I think that’s inefficient. I’m assuming you can do this with a measure, but I don’t know how to do it. Any ideas?

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

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Please see attached Excel 2013 workbook for one approach to solution.

    For this solution, no relationship between Calendar and Items is necessary.

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

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Thanks! This is an interesting approach. I’m trying to avoid adding a column for each “Item” because there will be countless items. Do you know of a way to accomplish this task with a measure?

    #1976

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Attached is a workbook that uses a measure.

    However, to write that measure I un-summarized you Items table by way of a “linkback” process that you can read about here and here.

    I have run into this scenario a couple of times where date ranges are summarized in a table of from and to dates which essentially bypasses what Power Pivot was designed to do: aggregate and summarize. Instead of using a linkback table, you could import you “items” as a two column table which has one row for each Item and Date pair.

    The calculation for the linkback table (which will refresh concurrently with the Items table) looks like this:

    EVALUATE
    SUMMARIZE (
    FILTER (
    CROSSJOIN ( Items, Calendar ),
    Calendar[Date] >= Items[Start Date]
    && Calendar[Date] <= Items[End Date]
    ),
    Items[Items],
    Calendar[Date]
    )
    ORDER BY
    [Items],
    [Date]

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

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Thanks for pulling this together! I really appreciate your help. This is another interesting approach. I like that the table used by PowerPivot is dynamic.

    #1993

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Another possibility instead of a linkback table would be to have Power Query transform the source for the Items table into the DateItems table and load directly into the Power Pivot model. This would have the advantages of overcoming the 1048576 row limit imposed by an Excel worksheet, plus it would reduce the number of tables in the my last attached workbook’s data model.

    #1996

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    That would be really great. Do you know what the query would look like to do that?

    #2000

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    An interesting link to follow in this direction would be “Automatically Ranged Date Table Using DAX and M” by Kasper de Jonge:

    http://www.powerpivotblog.nl/automatically-ranged-date-table-using-dax-and-m/

    #2137

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Thanks for your help! I ended up using a combination of the tricks (the DAX table from Kasper and power queries to dynamically join tables).

    One thing I struggled with was plotting data from one table column as a separate series and comparing that to a different field. See image attached. As a workaround, I made a measure for each unique value. Normally, you’d use the Legend (series) area of a pivot chart for that, but adding another field duplicates that data for each value in the series. Is there any easy workaround for that? I suspect you could use a relationship, but I’m not sure how to do it.

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

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Hi,

    That does sound like some extra work, but hard (for me) to address the issue without a workbook with sample data.

    Somewhat off topic for this issue, but more related to the DAX table from Kasper, in my copy of the code I have modified the line

    NumberOfDates = Duration.Days(EndDate-StartDate),

    to read:

    NumberOfDates = Duration.Days(EndDate-StartDate) + 1,

    When I initially created a calendar table with the original line of code and used StartDate of 1/1/2015 and an EndDate of 12/31/2015, the calendar table ended on 12/30/2015. Then I changed the Power Query code to add the 1.

    #2144

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Here’s a working example of what I’m trying to do.

    http://1drv.ms/1MBPhRu

     

    #2150

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Letting you know I have downloaded the file and, if you want to, you can remove it from your public drive.

    Currently chewing on a couple of ideas.

    Question: Regarding the Policy ID’s that are used in Policy City, Policy County, Policy State, and Policy Utility, is there a chance that they will conflict with each other?

    #2151

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Thanks! Regarding your question, no, IDs are only in one table: City, County, Utility, or State depending on their scope. Some policies are only for a particular city, others are for a county, etc. My idea is to use a pseudo-hierarchy allowing a user to look at a particular city and see all the policies that affect that city (including relevant county, utility, and state policies).

     

     

    #2159

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Nick,

    I experimented with a copy of your workbook, and although it looked promising for a while, when everything was said and done, the only way to get the data onto the chart as needed was to create measures for the individual policy IDs.

    My approach was to use Power Query to append Date and Policy ID columns from Policy City, Policy State, Policy County and Policy Utility. Once imported into Power Pivot, added a formatted column to display the IDs in a pattern like “ID 01” and created a relationship with the Calendar table. This worked great for getting the Policy IDs on the 2nd vertical axis, but without the individual policy ID measures, the chart never looked how I wanted it to.

    I appreciated the chance to work with you on this project. If I get an insight in the next couple of days for another way to come at the chart without so many individual measures, I will definitely post here.

    Best regards,

    Tom

    #2161

    npnigro
    Participant
    • Started: 1
    • Replies: 7
    • Total: 8

    Thanks so much for thinking it through, Tom! I really appreciate your insights and your time. In the end, adding ~50 measures was a pain once, but subsequent additions take < 1 min so it shouldn’t be too much of a problem. My objectives were to keep maintenance as simple as possible and to reduce unnecessary data. I think my approach will work for my needs.

    Nick

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

The forum ‘Power Pivot’ is closed to new topics and replies.