Home Forums Power Pivot Count Last Status Code Per Year

This topic contains 6 replies, has 2 voices, and was last updated by  tomallan 3 months, 1 week ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #9195

    brawnystaff
    Participant
    • Started: 3
    • Replies: 9
    • Total: 12

    I am trying to get count the last known customer status code for each calendar year.  I am able to get the last known status code for each customer overall using a measure, but I am looking to get a per year count.

    See attached workbook.  Sheet “Desired Output” is what I am trying to achieve.  Thanks.

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

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Brawny,

    What version of Excel (2013 or 2016) are you using?

    Tom

    #9198

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    R,

    Here is a potential issue with your request: Statuses do not always neatly fit within years. I have seen statuses start in one year and end in another.

    Let me give you a case: CustomerID 4 has only one status (say, 10) and that status spanned from 12/1/2016 to 12/31/2017. In such an instance, would your boss want to see CustomerID 4 counted in 2016 and in 2017 or just in 2016 or just in 2017?

    The formula depends, as well as the valid results, on your boss’s or customer’s decision.

    Tom

    #9199

    brawnystaff
    Participant
    • Started: 3
    • Replies: 9
    • Total: 12

    I am using Excel 2016..

    Regarding status, would just go with the Dates specified in “Date Through” column..Thanks.

    #9200

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Hi,

    With variables and the current requirements, Excel 2016 makes it easy!

    Please see attached workbook.

    A calculated column identifies the rows that have the last code for a customer in a year.

    The measure is simply a distinct count of customer ID’s of the data table filtered using the calculated column described in the last sentence.

    Tom

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

    brawnystaff
    Participant
    • Started: 3
    • Replies: 9
    • Total: 12

    Thanks tomallan.  That worked.

    Quick question out of curiosity:  You used a Calculated Column in a Measure.  It is possible to use a Measure in a calculated column?  (thinking not)..

    #9203

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    You may be interested to know that measures used in calculated columns possess a special power called “context transition”. The term often sounds somewhat daunting, but means that using a measure in a calculated column will create a filter.

    In a lookup (dimension) table, using a measure in a calculated column creates a filter on the relationship between the lookup table and the fact table. However, in a fact (data) table using a measure in a calculated column creates a filter on all rows of the fact table that have the same values in all columns.

    The above sentence will probably have more meaning if one just focuses on the effect in a lookup table: using a measure in a calculated column of a lookup table creates a filter on the relationship between the lookup table and the fact table.

    For a brief study, please see the data model in the attached workbook. I created a table for customers and related it to a fact table named Data.

    In the new customer’s table, I created two calculated columns: the first consists only of the formula “= SUM ( Data[Amount Paid] )”, but the second calculated column contains a measure that was created using the same formula. The first calculated column returns a total of all amounts paid for all records in the fact table. However, in the second calculated column, because it is based on a measure, applies a filter to the relationship between customer and their related rows in the data table and only sums amounts paid for a customer.

    You could also take a look at the worksheet “Measures in Calculated Columns” to see how the two calculated columns could play out in the Rows drop zone of a pivot.

    Attachments:
    You must be logged in to view attached files.
Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.