This topic contains 9 replies, has 2 voices, and was last updated by 
-
Posts
-
Once again I have struggled to long before asking fro help…. If anyone has thoughts on how to do this, would be in your debt!
Setup:
3 columns at play,
– a “requisition” number that is repeated in the column
– a Start Date that is repeated in the column (all requisitions have the same start date)
– a potential end date (potential meaning it could be blank), repeated in the column, and the dates are different.
trying to do a formula that would “pivot” on the “requisition number”, than find the most current (lastdate) in the column for that requisition number and subtract it from the start date to get to the number of days duration from start to last person completed.
I tried all sorts of variations using lastdate(), but either get errors around multiple dates in the column, or says invalid formula in the pivot (but checks out in the “check formula when creating the measure).
Anyone with thoughts on how to do this ?
THANKS!
Hello Istein8541,
I have done something similar using the EARLIER function, but I request some sample data so that I can save time in coming to a solution. The content of the data can be modified (made fictitious) as well as the table and column and table names, but the structure of your tables and relationships should be consistent. Also, many rows are not necessary, just enough to test.
Tom,
Again I appreciate all the help, and sorry for the delay.
After several discussion (ok arguments), I can’t gain permission to send the file, even in its obfuscated state, the opinion is that the headers are proprietary. So I think I am stuck (other than describing basic type headers and relationships).
Again, thank you for the time you have taken to assist, it is very much appreciated.
Hello Istein8541,
Attached is a workbook where I was thinking about what you are trying to do.
Can you make changes to the workbook and show me what you want?
Here you go, give this a shot, see if it helps…
thanks again for the help !
Thank you for the sample.
Knowing exactly what you wanted helped me to quickly find the answer (see attached workbook).
THANK YOU !!!!!!
I am almost there ! Can I can beg a little more help ?
The formula works at the requisition level, however when I and the geographic elements to the row now (e.g. Area and District), the sub totals and totals, reflect the largest number (of course — because we said min and max !).
I tried to add an average, but it wants a ‘column’ reference. I embedded the min max statements directly into the single formula, but that wasn’t helpful, as that just created a tad cleaner way of doing the same thing…
I tried to “cheat” and average the calculated field (but of course its the same thing)…
is there a way to use this math and get to an average?
Again, I cant thank you enough for the time you are investing is us far less skilled !
Hello lstein8541,
When you saw the error message that Power Pivot was exoecting “a ‘column’ reference”, you were probably using the AVERAGE function, which does require a column reference — but the good news is that there is another function with an X at the end: AVERAGEX, similar to AVERAGE, but it works with eXpressions as well as column references.
I tried that too, but got an column reference error, also tried it using “Values” as an expression after average. So not thinking I do not have my syntax correct. The one that works is the same, just without the ‘Average’ functions.
Do I need to do this as a Calculate? If yes, how, I don’t need any filters, as we are performing that in the ‘if’ for the blanks.
As always.. thanks…
Sorry, please ignore last post… As soon as I hit the send button, I realized the error, as I was referencing the column not the table. But even one repaired, the syntax, while works has the same result as not using the avergaex, it is still grabbing the “max” number for the roll up….
I think the whole statement has to be in a average(x), but not sure how that syntax would look?
The topic ‘Subtracting 2 dates using lastdate()’ is closed to new replies.