+ Reply to Thread
Results 1 to 18 of 18

Previous from column to the left dax

  1. #1
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Previous from column to the left dax

    HI

    question

    I'm looking for a dax formula that does the following

    i have a pivot table that looks like this

    customer number ,customer name ,Total bx sold
    1234 jacks 53

    Then i insert a slicer and i filter the total bx sold to the last 12 months

    I would like to add another column that would give me total bx sold for the 12 months before the last 12 months

    Any help is greatly appreciated
    Last edited by BORUCH; 07-25-2018 at 11:44 AM.

  2. #2
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    P.S.

    The DAX formula I’m using to get the month number to filter the last 12 is a calculated column

    =12*(YEAR(TODAY())-YEAR([invoice date]))+MONTH(TODAY())-MONTH([invoice date])

  3. #3
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    Any updates on my question ?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,584

    Re: Previous from column to the left dax

    BORUCH, I'm just guessing it would help if you upload an Excel workbook representative of the challenge. Verbal descriptions often leave too many vagaries.


    If you don't know how to do this:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  5. #5
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    Hi All

    I Attached my file any help is greatly appreciated
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    any updates to my question?

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,658

    Re: Previous from column to the left dax

    Your file is not usable since it is missing source data.

    You can use helper column with formula in your Pivot Source data to arrive the expected results in pivot.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  8. #8
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    its not missing any source data the source data is in the first sheet

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,584

    Re: Previous from column to the left dax

    I wasn't able to use the file either. Excel indicated there weren't enough resources.

  10. #10
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    i don't know what the problem is i am able to open it its a power piviot data model

    maybe someone else can help me with this question

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,496

    Re: Previous from column to the left dax

    What are your expected results in the two cells containing question marks (????) and why?

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    3
    ITEM NUMBER
    ITEM DESCRIPTION
    Total Qty sold
    12 months before the last 12 months
    4
    8888
    ORANGES
    20
    ????
    5
    9999
    APPLES
    27
    ????
    6
    Grand Total
    47
    Sheet: Sheet4
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,427

    Re: Previous from column to the left dax

    I think you probably want to look at the SAMEPERIODLASTYEAR function, coupled with DATESBETWEEN or DATESINPERIOD. I only have 2010 available so can't work with your file currently, but have a look at this: https://www.sqlbi.com/articles/compa...eriods-in-dax/
    Last edited by rorya; 08-07-2018 at 05:37 AM.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,496

    Re: Previous from column to the left dax

    Rory - these functions are not native to Excel. Is it possible you are using UDFs with these names?

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,427

    Re: Previous from column to the left dax

    They're DAX functions which is what the OP asked for.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,496

    Re: Previous from column to the left dax

    Ah ... OK.

    More coffee!!!

  16. #16
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    Hi

    Can someone with excel 2016 maybe try to help me with my question?

    My expected results in the two cells containing the question marks would be, the number of months before the Colum to the left.

    So, for ex. if i filter the last slicer to the last 8 months the next Colum would be 8 months before that

  17. #17
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    136

    Re: Previous from column to the left dax

    Is there any way i can get an answer ?

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,496

    Re: Previous from column to the left dax

    It looks as if you are out of luck.

    We really don’t get many questions about DAX at all, and I’m not sure how many of the members who help here use it.

+ 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. Replies: 6
    Last Post: 06-26-2017, 09:04 PM
  2. Macro that moves Formulas 1 column right and then Pastes Values into previous column
    By ExcelDavid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2014, 05:30 PM
  3. [SOLVED] Formula that starts where previous row left off
    By jwortsman in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-06-2012, 09:47 AM
  4. Insert column and fill column upto where data is in previous column
    By aka189 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-24-2012, 06:07 AM
  5. Replies: 2
    Last Post: 05-18-2012, 12:34 AM
  6. lock preceeding cell if previous left blank
    By kaspr79 in forum Excel General
    Replies: 1
    Last Post: 06-26-2008, 01:42 PM
  7. Working days left in the month compared to previous months
    By qwopzxnm in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2005, 03:00 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