locked
Create New Calc. Column Populated with Value from Prior Period RRS feed

  • Question

  • Cheers all!

     

    Needing some quick assistance per the below. If anyone can show me how to do this via creating a CALC COLUMN or via doing it via POWER QUERY 'M' code I would be most thankful.

     

    • Using Excel 2016 Office 365 64bit, latest version
    • Very simple data model

     

     

    I have a FactTable displaying sales reps with corresponding columns of data. Sales reps are identified via unique manner via their Badge#. They are also categorized by a column called Queue (five unique queue types). Multiple months of data appear in the FactTable, so reps appear multiple times defined by the Date column. Simple stuff.

     

    However, these reps can change Queue types at the start of any given month. Therefore, I'm trying to create a new column called "Prior Month Queue" that will display the queue for that sales rep for the prior month period. Of course, at any time a sales rep may not have prior month queue info (i.e., they were just hired on any given month).

     

    I also have a Periods table with Dates (this is a dim lookup table of course). FYI, everything is based on a fiscal 4-4-5 calendar, and this happens to be a year with 'an extra week' in it.

    FYI, I am unable to include images of the data model due to my account has not been verified yet.

    Suggestions? Again, this can be done via 'create new CALC COLUMN' or via 'Power Query 'M' code' if you can supply that.

     

    Many thanks!

    Saturday, December 30, 2017 7:28 PM

Answers

  • Hi there,

    It is can be done by in DAX by calculated column or measure. You can also get it done via M.

    I just provide an solution with calculated column in DAX

    Based on your description I simulated a dummy data as following and you can see the calculated column to return the value as you look for.

    Kind regards,

    Tom Sun

    • Marked as answer by DistinctlyDAX Sunday, December 31, 2017 7:42 PM
    Sunday, December 31, 2017 10:07 AM

All replies

  • Hi there,

    It is can be done by in DAX by calculated column or measure. You can also get it done via M.

    I just provide an solution with calculated column in DAX

    Based on your description I simulated a dummy data as following and you can see the calculated column to return the value as you look for.

    Kind regards,

    Tom Sun

    • Marked as answer by DistinctlyDAX Sunday, December 31, 2017 7:42 PM
    Sunday, December 31, 2017 10:07 AM
  • Tom,

    Many thanks, this worked perfectly! I've marked it as 'correct answer'. Cheers and Happy New Year!

    Sunday, December 31, 2017 7:43 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With fiscal 4-4-5 calendar and real dates.
    Most 'Time Intelligence Functions' can't be used with 4-4-5.
    Used text queues.
    http://www.mediafire.com/file/2yp1gxpdu2tigbv/12_31_17.xlsx
    http://www.mediafire.com/file/bc5em1d00f8nsyo/12_31_17.pdf

    Monday, January 1, 2018 2:57 AM
  • Herbert,

    You of course are correct. However, my dimPERIODS table contains a unique sequential 'PeriodID' column, which is 1, 2, 3, etc.  So I can perform basic math on that column per the suggestion from Tom Sun and his solution to solve the issue.  Thanks!

    Monday, January 1, 2018 5:57 AM
  • How do you handle the transition from FY2018 Period 1 (Apr) to FY2017 Period 12 (Mar)?
    Monday, January 1, 2018 2:37 PM