[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

I think I have it.  The only way I could do it was to add a calculated column to my data table which gave the number of months between the File Date and Maturity Date.  I used this formula =(YEAR([Date2])-YEAR([Date1]))*12+MONTH([Date2])-MONTH([Date1]).  Date 2 = Maturity date and Date 1 = File Date.  I added a pivot table filter using this new calcuated column and selected values 1 thru 6.  I tested the results selecting different File Date values via the slicer and it works.  Now just need to figure out how to get the months to show in chronological order.  The sort column Newest to Oldest isn’t cutting it.

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

You are correct, it was the use of earlier and an issue with my SUMX structure.  SUMX was missing the table reference and EARLIER was not the correct tool for the job.  I switched out earlier for MAX and got the formula to validate with no errors.  Dropped it into the Values frame and ran into another issue which was that the measure output was the same for each row in my pivot table.  I removed the ALL(mytable) and voila, I think I have what I need.  Still need to do some testing but the results pass the eyeball test for now.  Here is my final result for weighted average with filters with deletions showing with strikethrough and additions to the formula showing as bold.

=CALCULATE(SUMX(mytable,mytable[$ value]*mytable[# code])/SUM(mytable[$ value]),FILTER(ALL(mytable),mytable[Last Refresh Date]=EARLIER(MAX(mytable[Last Refresh Date]) && mytable[special code]=”YES”))

 

Thanks for the direction.

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

I hope to get there someday, helping instead of begging….to that end I’m sure I’ll have other learning opportunities for you in the near term.

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

Thanks, this worked wonderfully!  You can really see it on a 13 month trending graph which is now smooth ups and downs as opposed to the sharp jumps that were present before.

Thanks for your help.

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

Exactly.  Probably more info that what you need but to give you a background of the application here this is not for NAICS codes as the formula you sent me is applicable for many other system codes beyond just NAICS codes.  This new request is for the code that we use to determine the type of loan which is also an area where a good bit of data scrub work gets done which is why your formula is also very useful in this other type of coding.  The catch that I ran into when using your formula for this other coding was that we have some codes that are intentionally transient in that they start as one type of loan and then at some point they switch.  A construction loan is an example, where we start off with a loan that is coded as a construction loan, which is the correct coding, and then the construction is complete and we now have a functioning apartment complex for example.  If I use the coding you provided then the loan type code would get overwritten as a finished apartment complex from day 1 when in reality it should show as construction for the first nine months with a code switch occuring once the project is complete.  In this case I will use this additional logic in front of the formula you previously provided for the logic to return the code on the system for each refresh date if there is a code of 120 (which is the construction code) for any of the refresh dates.

Once again, way more than you probably want to know but thought it might help explain what I am trying to do and why I would want to suspend the logic for certain codes.

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

Thanks for the tour!  Very helpful.

I do have a follow up question related to the formula at hand.  How would I exclude a certain NAICS codes from being processed in this manner.  I tried a nested if statement ahead of the formula to preempt the formula.  For example, I said that if a NAICS =555555 then rely on the NAICS code provided and if false leads to your equation.   As I think of it though I believe what I really need is something more complex because what I really need is to keep the NAICS codes as they are for each refresh date when code 555555 exists in any of the refresh dates and if not then follow your coding.

Thanks for your help.

 

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

Thanks Tom, this is impressive.  Exactly what I needed.  I will definitely be adding this arrow to my DAX quiver.

Just so I can make sure I understand what I have here, I would like to describe what you have written and get your feedback my understanding of each component of the formula.

Starting from left to right, CALCULATE is as I understand it the gateway to all good things in DAX (I know there is more than that but I know it allows for a summary calc with filter capabilities).  HASONEVALUE was used as the true false test for the if statement.  It is testing how many variations of the “NAICS” filter are present.  If it is a distinct value the the true result caluclates the max “NAICS” per the MAX operator.  MAX operator is there as you said to keep everyting kosher per the CALCULATE operator requirements and to supress dupes.  If the statement were false, and there are multiple “NAICS” present then the TOPN operator is depolyed to return the nth record, which you have specified as the first per the “1” at the beginning of the equation.  The FILTER and ALL sets directs the TOPN to the table to be analyzed and the ALL clears any filters in place.  The [Account#]=EARLIER([Account #]) section sets the account # as the expression that will drive the ordering which will be done by the [Data Refresh] field at the end of the formula.  In doing some research on EARLIER which was new to me, I found a post http://www.powerpivotpro.com/2012/03/the-correct-usage-of-earlier/ that describes EARLIER as the escape hatch for the ALL operator as the ALL operator sets the context to a column order and EARLIER can be thought of as CURRENTROW as it sets the context back to a row order which is what TOPN needs.

Let me know where I missed the mark with my comprehension of these concepts.  So much to learn!!

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

64 bit 2013

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

Thanks Tom!

I work for a bank so I had to create a dataset for you.  It is a very simple dataset however I believe it gives you what you need to generate a solution.  With that said, all of the columns provided are actual columns found in my fact table along with 20 or so additional columns of data not pertinent to this request.  I use this fact table to trend our loan portfolio over many months worth of reporting data by balance and other metrics.

In reference to the problem at hand, the example provided is a very prevalent issue whereby we have determined that a NAICS code is incorrect and have corrected the coding as is seen in the sample dataset where the NAICS code changes somewhere in the month of April.  I would like to be able to create a measure that overwrites the NAICS code for previous reporting periods that contained the incorrect code in order to limit the noise in the reporting.  I still want the incorrect coding in place because even though it is incorrect I need it to report as such for audit purposes.  Could even contrast the two (actual vs. retro corrected) to report on the total $ worth of accounts that have been scrubbed/corrected over a period of time.

 

Thanks for your help.

Attachments:

You must be logged in to view attached files.

[Author]

Posts