+ Reply to Thread
Results 1 to 4 of 4

Overlay YoY per day

  1. #1
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    18

    Question Overlay YoY per day

    Creating YoY data is easy by month by using Year in the Legend of the pivot chart.
    What I need to do to overlay YoY data by day.

    I have daily values of 16 months of data: January-2023 to April-2024.

    Is it possible to overlay January-February-March-April data for 2023 and 2024?
    Preferably to overlay the same days of the week.

    For example, can I have 2 X-axis: the bottom axis for 2023 and the top axis for 2024?

    Is there an alternative solution in PowerBI?

    Attached is an example of the data.

    I'm using Microsoft 365 version 2402.
    Attached Files Attached Files
    Last edited by edg_r; 05-11-2024 at 03:30 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    82,886

    Re: Overlay YoY per day

    Administrative Note:

    Is your forum profile showing the Excel PRODUCT that you need this to work for?

    Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

    The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-16-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Overlay YoY per day

    I would appreciate if someone could help.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,770

    Re: Overlay YoY per day

    It might be easier to understand the request if there was an example of the expected output.
    That said here is a proposal that adds two columns to the source table.
    1. The Day column is populated using: =TEXT([@DATE],"ddd")
    2. The Match column is populated using: =IF(ROW()=2,0,IF(YEAR([@DATE])=YEAR(INDEX([DATE],ROW()-2)),SUM(D1,1),1))
    In the pivot table pull the Match and Date fields into the Rows area, Value into the Values area twice once for sum and the other for difference, and the Day field into the filter area.
    The Match field is filtered to show matches between 1 and 121 which is the max number in 2024.
    The max number is calculated in cell H1 using: =AGGREGATE(14,6,Table1[Match]/(YEAR(Table1[DATE])=2024),1)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Overlay worksheets with formatting
    By danellis in forum Excel General
    Replies: 3
    Last Post: 06-08-2016, 06:35 AM
  2. Compare for overlay
    By Indi_Ra in forum Excel General
    Replies: 0
    Last Post: 03-28-2015, 04:15 AM
  3. Create an overlay
    By Ugabuga in forum Excel General
    Replies: 3
    Last Post: 10-05-2014, 02:06 PM
  4. Overlay, but not the formula
    By tporter337 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-29-2011, 09:19 PM
  5. Making Gridlines Overlay
    By gvb in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-14-2009, 08:30 AM
  6. copy but don't overlay
    By mwc0914 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-15-2005, 03:02 PM
  7. [SOLVED] Overlay charts
    By Ian in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 03-24-2005, 04:06 PM

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