February 13, 2018 at 5:40 pm #9195
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.February 13, 2018 at 5:54 pm #9197
What version of Excel (2013 or 2016) are you using?
TomFebruary 13, 2018 at 8:50 pm #9198
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.
TomFebruary 13, 2018 at 9:51 pm #9199
I am using Excel 2016..
Regarding status, would just go with the Dates specified in “Date Through” column..Thanks.February 14, 2018 at 12:07 am #9200
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.
TomFebruary 14, 2018 at 1:39 am #9202
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)..February 14, 2018 at 1:52 pm #9203
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.
You must be logged in to reply to this topic.