+ Reply to Thread
Results 1 to 6 of 6

PowerPivot - Show all years for a measure and latest year for another

  1. #1
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    PowerPivot - Show all years for a measure and latest year for another

    hi people! so i posted a question previously and it probably had too many questions. see if i can break it down into many parts.

    so in the Excel file, i have 3 tables. 1 is the revenue for F2F. 2 is the revenue for Online. 3 is the Date table.

    how do i get the chart to show ALL years and YTD revenue for F2F (2015 to 2019), but just latest YTD for Online (2019)?

    my F2F Revenue would be: Revenue YTD = TOTALYTD(SUM(MainTb[Revenue]),DateTb[Date])
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: PowerPivot - Show all years for a measure and latest year for another

    Hi

    to find 2019 YTD only

    Onine YTD = Calculate(TOTALYTD(SUM(Table2[Revenue]),DateTb[Date]),Filter(DateTb,DateTb[Year]=2019)

  3. #3
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    Re: PowerPivot - Show all years for a measure and latest year for another

    thanks, Sadath. it almost worked. the chart shows 2019 only correctly. but i want it to show the latest year based on my data. currently, my data is up till 2019. but it will go to 2020, 2021, etc. i tried with MAX but it doesn't work:
    Onine YTD = Calculate(TOTALYTD(SUM(OnlineTb[Revenue]),DateTb[Date]),Filter(DateTb,DateTb[Year]=MAX(DateTb[Year])))

  4. #4
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: PowerPivot - Show all years for a measure and latest year for another

    Hi

    use this

    PHP Code: 
    Onine YTD :=
    VAR 
    MDt =
        
    CALCULATE MAX DateTb[Year] ), ALL DateTb ) )
    RETURN
        
    CALCULATE (
            
    TOTALYTD SUM OnlineTb[Revenue] ), DateTb[Date] ),
            
    FILTER DateTbDateTb[Year] = MDt )
        ) 

  5. #5
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: PowerPivot - Show all years for a measure and latest year for another

    Hi
    alternate formula

    HTML Code: 
    this will take last date from the OnlineTb not from the DateTb

  6. #6
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    Re: PowerPivot - Show all years for a measure and latest year for another

    that is great. thank you so much!

    could you explain why mine didn't work and yours did? i think mine didn't work because when chart is showing Year 2014, the MAX of DateTb[Year] is 2014 and not 2019?

    but why did yours work? is a variable only returning 1 single value?

+ 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. [SOLVED] Formula to show the latest date in a given year from a column of dates
    By Mgc26133 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2018, 10:00 AM
  2. PowerPivot measure
    By KriZo in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-20-2017, 12:56 PM
  3. New Measure Powerpivot Unique Count
    By PatSc in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-26-2014, 01:36 PM
  4. Powerpivot table add new measure
    By Kevin_M in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2014, 07:36 AM
  5. Powerpivot - Returning latest update based on latest date
    By Kehjz in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-22-2013, 02:45 PM
  6. [SOLVED] Powerpivot Rolling Average Calculated Measure
    By hbusche in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 07-15-2013, 06:46 PM
  7. PowerPivot Measure Formula
    By Twi78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2012, 08:45 AM

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